Invalid Use of Null when applying any criteria to a non-null table

[rbmdr]

CountOfProgressItemDescription	Progress Date S4	Expr1
33	2/9/2019	Date
4	2/14/2019	Date
3	2/15/2019	Date
26	2/21/2019	Date
7	2/27/2019	Date
31	3/6/2019	Date
9	3/13/2019	Date
19	3/28/2019	Date

Hello. The above table is the output of a query that I am trying to put together for a project. Because the datasource table provides the Progress date field as a string, I have tried using both CDate and CVDate to convert the output of the query to a date. Both successfully convert the datatype to date (Expr1 is TypeName(Progress Date S4)). The dates work as intended when I sort by ascending or descending. When I try to apply any criteria however, I get an "Invalid use of Null" error. None of the values in the source data are Null, nor is the source query to the query that creates this table (the source query is just the count and CDate/CVDate values). This query is just select * and Expr1.

I am very confused why I keep getting a Null error when trying to run any type of criteria on this query output? I really appreciate any help.

[JoeM]

I have seen this sort of thing happen when the underlying table(s) to these data sources had records that have nulls in them. It took me a while to figure it out, because I had nested queries and all that, and none of my queries had null values. But the original table that the first query was based on had it.

The way I understand it, is while we have our queries going in a certain order, when Access procresses the query (and all underlying queries), it may restructure it behind the scenes to find the most "efficient" method. So these sort of things may creep up.

I think what I ended up doing is taking my last query (prior to the one with criteria), and changing it to an Append Query, and writing the results to a temporary table, which is what I would use in that query with the criteria. Then when finished, I would delete all the records from that Temp table so I could use it again next time.

Perhaps another alternative would be to write the query, with the thought that it still sees Null values in that field. You may be able to use the NZ function to default Nulls to some dummy data, and then just exclude that date from your query results.

[rbmdr]

Well, I was going to say that fixed it, but I changed something on a different version of the database I was using for testing, that had already fixed it in a second, non-feasible way. I will do some testing and come back.

[rbmdr]

Looks like the "Is Not Null" approach didn't work, but simply adding a nested nz in the cdate expression seemed to fix it. Thanks for the input.

[JoeM]

You are welcome.