Invalid use of null in where statement; no null values

Question

I have the following problem:

I am using two queries to parse imported data, they are both selecting data The imported dataset is pretty complex, but this reproduces the error.

QueryA:

SELECT CDbl(FieldA) As DblA, Imported.* From Imported WHERE FieldA IS NOT NULL

QueryB:

SELECT * FROM QueryA WHERE DblA > 7 AND DblA < 600

QueryA runs fine, QueryB throws an invalid use of null error

If I insert the results from QueryA into a table and run QueryB against that I do not receive the error, however, this is not desired.

If I remove the WHERE from QueryB it runs fine.

Is there a different workaround for this? Or should I just accept the redundant table?

Outmoded

Would NZ solve it? SELECT * FROM QueryA WHERE NZ(DblA,0) > 7 AND NZ(DblA,0) < 600. Any Null values are replaced with 0. – Hohenlinden

@DarrenBartrup-Cook NZ does not solve it. There are no null values in that column. – Outmoded

I'd suggest removing Imported.* from the QueryA first and then explicitly start adding fields back to QueryA. You might find a field in the dataset that ms-access is having an issue with. – Waters

Done that. The relevant field is unfortunately FieldA, I can reproduce the issue entirely without Imported.* – Outmoded

Answer 1

Ah, fixed it, still don't know why.

Changed QueryA

SELECT CDbl(Nz(FieldA, 999)) As DblA, Imported.* From Imported WHERE FieldA IS NOT NULL AND Nz(FieldA, 999) <> 999

As far as my SQL knowledge goes this shouldn't make any difference, but it does. If someone can explain it I would welcome it.

The 999 instead of 0 is because else I would get division by 0 errors in other functions (while really Access shouldn't be running any functions with it since it's filtered out).

Outmoded

I think the query optimizer is the problem. My guess is that it tries to evaluate the DblA > 7 AND DblA < 600 section before FieldA IS NOT NULL It does not run them as separate queries. This gives speed gains when selecting from a query that returns a lot of data, but can also give these strange errors when it doesn't run the query in the order expected. – Deferent

@Deferent makes sense, thanks for the explanation. I always thought that the optimizer doing weird things was an MS SQL thing and that Access just processed things in order, but I guess I was wrong. In one course I even learned I should use subqueries instead of referring to other queries, else the optimizer wouldn't work. – Outmoded

It makes sense to me that that is how it is doing things, but I have no documentation on it. Otherwise I would have posted it as an answer :) – Deferent

Answer 2

Try this query.

SELECT * FROM QueryA WHERE DblA > 7 AND DblA < 600 AND Dbla IS NOT NULL

Paranoiac

Nope, still invalid use of null – Outmoded

Why not we apply the range condition in Query A? SELECT CDbl(FieldA) As DblA, Imported.* From Imported WHERE FieldA IS NOT NULL AND FieldA>7 AND FieldA<600. – Paranoiac

There are multiple queries dependent on QueryA, and QueryA is in fact using an IIF statement, getting DblA from one of three fields. – Outmoded