Run time error ‘94’ invalid use of null

[Excelcurious23]

I see this error in the line docmd.openquery xyz

First question is how do I figure out which value is null ? The query xyz is a huge query. How do I see which value is nulll. This statement works for other situations except for maybe 1 out of 30 runs.

I’ve tried to import the tables involved in the query into another access database named them the same and Copied the same query and ram it and I got results !! So it is not a data issue

Help!!! Please advise on how to proceed and how to debug this !!

Usually hovering over a variable will tell us which value is nulll. But in this case all I know is the query name which has a null value but not which field in the query is null. !!

 

[Joe4]

Exactly how many fields are in this query?
How hard is it to make a copy of the query, take away all the field except one, and then add them back in one at a time, checking after each one, so you can identify the offending field?

 

[Excelcurious23]

. There are 3 tables and 20 fields each.

 

[Joe4]

. There are 3 tables and 20 fields each.

Seems do-able.
If you really have no idea which field is the offending field, that is probably the best way to go.

Alternatively, you may want to open each table directly, and sort each field, one at a time, and check for any errors or odd looking data.

Most likely, you have some data that is wreaking havoc with one of your calculations, so the key is to find it.
I have given you multiple ways of doing that. Now it is up to you to do it.