CODEWITHSUNDEEP
sqlms-accessms-access-2007
RobbG
RobbG

Reputation: 391

MS Access, how to find Entries that don't appear in another table?

I have an MS Access database with a table called Numbers which just contains a list of numbers between 0 and 100000 inclusive with no duplicates as the field Number (data type Number with length Long Integer) (Easiest way I could fine to create a range of numbers within Access). I also have a query called AssetNumbers which extracts the numbers contained within a text field of another table as AssetNumber (return of Vba function, also Long Integer), it's likely there are duplicates within this data set.

I need to find which numbers are present in the Numbers table which don't appear in the AssetNumbers table, I have created a query using the Query wizard within Access which has given me the following query. This appears to match up with other examples I have found through searching.

SELECT Numbers.Number
FROM Numbers LEFT JOIN AssetNumbersON Numbers.[Number] = AssetNumbers.[AssetNumber]
WHERE (((AssetNumbers.AssetNumber) Is Null));

When I try to view the output of this query Access throws a "Data type mismatch in criteria expression" error. What is causing this error and how can I make this work?

Upvotes: 0

Views: 65

Answers (1)

RobbG
RobbG

Reputation: 391

The issue was having the comparison WHERE (((AssetNumbers.AssetNumber) Is Null)), I assume this was because AssetNumber was the return from a function rather than a static value. I managed to work around this by using another field from the AssetNumbers table for comparison.

Upvotes: 0

Related Questions