By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,071 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Invalid Use Of Null - No Null Values in table

P: 8
I have a query that looks like this:

SELECT qryPhysicals.NAME, qryPhysicals.UPC, qryPhysicals.SSN, qryPhysicals.PhysDate, qryPhysicals.YearMo, qryPhysicals.TimeElapsed
FROM qryPhysicals
order by qryPhysicals.TimeElapsed desc

This works fine.

However, if I add a where clause,

SELECT qryPhysicals.NAME, qryPhysicals.UPC, qryPhysicals.SSN, qryPhysicals.PhysDate, qryPhysicals.YearMo, qryPhysicals.TimeElapsed
FROM qryPhysicals
Where qryPhysicals.timeelapsed 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
Nov 25 '08 #1
Share this Question
Share on Google+
3 Replies


P: 8
I created a make table query, from the values then ran the query from the tbl, it worked out well.
Nov 25 '08 #2

P: 50
@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(qryPhysicals.timeelapsed ) > 0

That way you can catch nulls and empty strings.
Nov 25 '08 #3

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

If Len(strStringField & "") > 0 Then

OR

If Not Len(strStringField & 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.
Nov 26 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.