Type mismatch in Expression Access 2013

Well, that's a bit like asking a doctor "Why don't I feel well"? You can see your database - we can't!

Performance can be affected for all sorts of reasons: hardware, network architecture, the design of the queries, indexing on the join and sort fields, on and on. Joining on an expression such as Val(Product_ID) will slow things down drastically right there, since Access must call the function for EVERY ROW in the table, and cannot use indexing to speed up the join. And if you are in fact joining tables in separate .accdb files, that will drastically slow things down as well.

Could you post the SQL view of the queries, and some information about the tables on which they are based?

Thank you John!

I'm hoping I can pick your brain a bit further.

Now that I have this set up (and working!) I have designed a Query that is taking hours to run.  Maybe there is something simple I can do.  Here is what I put together:

3 links

Table 1 linked via Product_id to Table 2

Table 2 linked via Product_id to Table 3 (Table 3 is a query where I linked Product id to Item No using the method given)

Table 3 linked to table 4 via Item No

I am pulling into the query product ID from table 2

6 items from table 1

and Cost from table 4

Table 3 I am simply using to link table 4 with tables 1&2

any thoughts on why this is running so slow?

Note that the format of a field just controls how it's displayed, and is irrelevant for the joining. I think the problem is not with the formatting of the fields, but with the actual datatype - Number and Text respectively.

And, are you joining table in two different databases - separate .accdb files, each a container for multiple tables, forms, queries and other objects? Or are you joining two tables in one database?

Assuming the latter, you will need to build the query in SQL (the grid isn't up to it):

SELECT <whatever you want to see>

FROM Table1 INNER JOIN Table2

ON [Table1].[Product_ID} = Val([Table2].[Product_ID];

And you have my permission and encouragement to flog whoever set up these tables with a wet noodle for using inconsistant datatypes!

Please don't piggyback your question on another thread. Please use the Participate link above to start your own question. You can link to the older thread if you feel necessary.

When you do please identify the datatypes for the different fields. You can find then in Design mode. If the Datatype states Number, make sure you check the Size property for the subtype.

Hi John,

I'm trying to utilize this same logic in a mismatch error I have and can't get it to work. Here is my SQL created in Access:

SELECT <BAN1>

FROM 1ecars_monthly_accrual INNER JOIN ban seg lookup

ON [1ecars_monthly_accrua].[BAN1} = Val([ban seg lookup].[BAN];

Table 1 = 1ecars_monthly_accrual

Table 2 (joined table) = ban seg lookup

I want to join the field name BAN from ban seg lookup to the field BAN1 from Table 1. I would appreciate the help!