Why am I getting an "Invalid use of Nulls" error in my query?

Question

I use a userform in Excel to run some internal queries between my spreadsheets. However I am getting Invalid Use of Null. I am aware of the nz null syntax (SQL MS Access - Invalid Use of Null), however my queries can be quite large, and I am wondering if there is anything I can add to my VBA code to allow nulls.

Answer 1

Casting functions, like CStr, are very prone to trigger Invalid use of null. There shouldn't be a need to use CStr in joins, as it will typecast to get equality.

Remove the CStr on the following locations:

on cstr(map_transit.[Transit])=cstr(master.[Transit # (not rollup)])

and

on cstr(map_rfcurve.[Currency])=cstr(map_curr.[EnterpriseCurrency]))

Note that, while you've filtered with Is Not Null in those subqueries, the optimizer might first do the join, then throw an Invalid Use Of Null, before filtering. See this answer for a sample case where the optimizer did something similar.

Alternately, if you really want to cast to a string, you can use the following:

on (map_transit.[Transit] & '' = master.[Transit # (not rollup)] & '')

Appending an empty string to something casts its value to a string, even if it's Null, without throwing an error.