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.

Schellens

What about ISNull() function? – Possie

We'd need to see the SQL being executed against the database. I'd guess one of your parameters being passed in isn't handling null correctly so something like where SomeField = NULL is occuring when you need it to be where somefield = nz(Parm,'') But as it stands not enough info to really help. – Max

I see two options; in each selected value in the outer most select wrap the field name in NZ(fieldName,'') to ensure all nulls are handled there: 2) wrap each selected value in the inner queries in the NZ(FieldName,'') to ensure nulls are handled there. The problem will occur anytime you have a NULL value being compared via string comparisons such as IN, = <>, Now in the subqueries where you're ensuring the value is not null; you're fine on; it's one of the values you're not ensuring is not null; which would take some time to figure out – Max

Might start with adding NZ here... select [poly_EUC_ID],nz(RiskReportProductType,''),PolyMaturity from [mapped_polytypes$] then the fields involved in each switch statement if not handling the null's in inline view/derived tables. when using NZ in a query you'll need to re-alias the column to the same name again however... – Max

@Max , I tried for this one line, not sure if I got to add this to them all, but i am now getting error Undefined function 'nz' in expression – Schellens

techonthenet.com/access/functions/advanced/nz.php shows NZ being used as a function in a SQL statement to return the first non-null value NZ(FieldName,'') Where did you try it in the RiskReportPRoductType line? and alias the column RiskReportProductType? – Max

Sorry , I only did it on the Select Poly EUC ID line. – Schellens

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.

Housemother