Invalid Use Of Null - No Null Values in table

[JM420A]

I have a query that looks like this:

SELECT qryPhysicals.NA ME, qryPhysicals.UP C, qryPhysicals.SS N, qryPhysicals.Ph ysDate, qryPhysicals.Ye arMo, qryPhysicals.Ti meElapsed
FROM qryPhysicals
order by qryPhysicals.Ti meElapsed desc

This works fine.

However, if I add a where clause,

SELECT qryPhysicals.NA ME, qryPhysicals.UP C, qryPhysicals.SS N, qryPhysicals.Ph ysDate, qryPhysicals.Ye arMo, qryPhysicals.Ti meElapsed
FROM qryPhysicals
Where qryPhysicals.ti meelapsed is not null (or any other value)
order by timeelapsed desc

I get "invalid use of null"

I looked at all of the records, all of them contain values.

Any pointers are greatly appreciated.

Thanks

JM420A

[JM420A]

Figured it out

I created a make table query, from the values then ran the query from the tbl, it worked out well.

[Krandor]

Where qryPhysicals.ti meelapsed is not null (or any other value)
JM420A

I don't generally use IS NULL or IS NOT NULL because there are instances where you won't get the results you expect. Example: if the record has an empty string ("") that is not a null so if you are testing for null, you will miss it.

I found it is better to do something like this:
Where Len(qryPhysical s.timeelapsed ) > 0

That way you can catch nulls and empty strings.

[rsmccli]

Indeed. Another trick you can do is to add a null string to whatever value you are testing like so...

If Len(strStringFi eld & "") > 0 Then

OR

If Not Len(strStringFi eld & vbNullString) > 0

...et cetera. One of the Access MVPs (don't remenber which one o_O) told me that doing this makes the Null/Empty String check work in basically every situation.