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]

Welcome to the Board!

Is "xyz" the actual name of the query, or is it a variable?
If it is the actual name of the query, it need to be enclosed in double-quotes (see: DoCmd.OpenQuery method (Access)).

Can you also post the SQL code of this query so we can inspect it, and see if you have any ovbious errors?

 

[Excelcurious23]

Welcome to the Board!

Is "xyz" the actual name of the query, or is it a variable?
If it is the actual name of the query, it need to be enclosed in double-quotes (see: DoCmd.OpenQuery method (Access)).

Can you also post the SQL code of this query so we can inspect it, and see if you have any ovbious errors?

It is just an example name I’ve given. As I mentioned this works in 90 per cent of the runs.

 

[Joe4]

Usually, when I encounter an error like this, I start from the beginning.
First, check for any errors in the tables you are using.
Then, check the fields you are joining on carefully for anything amiss.

Then, start re-building the queryfrom the very beginning.
First, create all the joins between your tables, and add one field, and check to see if that works.
Then, add in all your criteria, and see it that works (note that when you find something that does not work, then you can pinpoint where the error is).
If that works, then add all the other fields which are NOT calculated fields.
If that works, then add in your calculated fields.

Eventually, you should stumble upon where the error is occurring. Once you get it, revert back to the previous step, and try adding in the things you added in the last step one at a time, until you find the offending piece.

 

[Excelcurious23]

Usually, when I encounter an error like this, I start from the beginning.
First, check for any errors in the tables you are using.
Then, check the fields you are joining on carefully for anything amiss.

Then, start re-building the queryfrom the very beginning.
First, create all the joins between your tables, and add one field, and check to see if that works.
Then, add in all your criteria, and see it that works (note that when you find something that does not work, then you can pinpoint where the error is).
If that works, then add all the other fields which are NOT calculated fields.
If that works, then add in your calculated fields.

Eventually, you should stumble upon where the error is occurring. Once you get it, revert back to the previous step, and try adding in the things you added in the last step one at a time, until you find the offending piece.

I tried doing this in a test database. I imported the tables I need. And tried to build the query from scratch. And surprisingly it works. But when the same query is run from another database it does not work. And I see the run time error.

I’m completely lost as how to fix it. This same code has been used since the last 15 years. So I’m afraid to make any coding changes as well.

 

[Joe4]

But when the same query is run from another database it does not work.

Do you mean that you are in one database, and trying to run a query against data in a separate database?

Or do you just mean that you are trying the same thing in another database and it is not working?
If that is the case, you need to follow the steps I gave you in the database that you are having issues with.

Also, do both databases have the EXACT same data?
If not, that is a good indication that it is a data issue!
If you follow the steps I gave you, you should be able to pinpoint which field in which table has the data issue.

 

[Excelcurious23]

Do you mean that you are in one database, and trying to run a query against data in a separate database?

Or do you just mean that you are trying the same thing in another database and it is not working?
If that is the case, you need to follow the steps I gave you in the database that you are having issues with.

Also, do both databases have the EXACT same data?
If not, that is a good indication that it is a data issue!
If you follow the steps I gave you, you should be able to pinpoint which field in which table has the data issue.

The issue is in an existing database with lots of tables and queries. I did not want to introduce errors during my troubleshooting process ( which u mentioned ). So I created a test database imported the tables I need. Copied the same access query and it works. Why does the same query not work in the existing database?

 

[Joe4]

Without having full access to it, it is hard to tell.
But it could be a case of database corruption (that does happen from time-to-time).

Try creating a new blank Access database, and then import all of the objects (tables, queries, forms, reports, VBA code, etc) from the old one to the new one.
If you do that, and then try the same operation in the new database, do you experience those issues?

 

[welshgasman]

Use the NZ() function and a unique replacement.
Or create a query that checks for null in the fields you are working with.
Or start with one field and add another until you get the error. Of course, you might have null in more than one field.

 

[Excelcurious23]

My initial thought was to use NZ but problem is I don’t know which field in which table has the null