Today I had an annoying error message occurring under cetain circumstances while creating a temporary table for an Access report to use. The error message was:

Run-time error ’94’: Invalid use of Null

Here are the steps I took to debug the error.

1. Make sure I can repeat the error when I do the same thing on the live system. If you can’t repeat it, you are shooting in the dark. You will just change random things and even if it starts working, you may not know why or whether that will keep it working. I usually don’t even bother trying to debug things if I can’t recreate them. If it’s a serious bug, you can take steps here to keep testing and TRYING to recreate it, but if you can’t, it’s unlikely you will be able to fix it.
2. In my case I have a development environment at home. If I can recreate the error on the live system, I copy over the live data to my dev system and try to recreate it the same way there. In this case I was able to recreate the same error on my dev system.
3. Think about the error, does it give any clues to why things aren’t working. In my case the Invalid Use of null I figured that a field must be null that the query was acting on. I didn’t know what line of code or query was acting when the error was thrown, so I needed to find that out next.
4. Place “Stops” into your VBA code to find the line that is causing the problem. You can use the F8 key to step through your code and shift-F8 to remain in the same context (rather than descend into functions or subroutines) and move to the next line. I found in my case where the error was being thrown, which was when it was trying to create a particular temp table out of a complex query.
5. Look at the query causing the error. In my case a complex SQL statement was being created that was using the INSERT INTO SQL clause to insert a record set into a new table.
6. Separate the query into pieces if applicable. I needed to look just at the subquery that was creating the data set to be imported into the table. I copied that SQL into a new query and was able to open it and look at the data set. At this point, it did not look like anything was wrong with the data set.
7. Sometimes you can get more information by trying to run the whole statement as a new saved Access query, so I did that, but did not get any new info in this case. It was the same exact error.
8. Break the query into smaller pieces. This might mean looking at different sub-selects separately in the query or removing some of the fields in the select clause and trying it again.
9. As I tested each individual piece of the query, I found that there were errors in the underlying queries that were not being shown in the final complex query. These errors were indeed due to functions being used to convert values to the Long data type that sometimes were null. My final solution in this case was to wrap the values I was trying to convert for this field in the Nz funciton like so, which uses another value if the original value is 0: Nz([fld],0)

This took me a long time today, over 2 hours actually. Don’t be frustrated with the process. Just get curious and keep breaking things down until you find an apparent reason for the error and test your hypothesis. Good luck!