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

SQL statement with <> and Null values in the table

P: n/a
I have the following SQL statement in code:

strSQL = "SELECT * FROM TABLEA WHERE F2 = 'SITE'

This returns the record that I expect.

However: the following does not

strSQL = "SELECT * FROM TABLEA WHERE F2 <'SITE'
But if I rewrite it as:

strSQL = "SELECT * FROM TABLEA WHERE Nz(F2,'') <'SITE'

that seems to retrieve records where there may be nulls in column F2.

Let me just say that this table is a holding table for an Excel import so
values could be anything.

Is this correct. That is, I have to use the Nz function when using <>. I
haven't been doing this in some other SQL queries and now realize that I may
be missing some rows that should be returned.

Can someone explain this to me?

Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 23 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a

rdemyan via AccessMonster.com wrote:
I have the following SQL statement in code:

strSQL = "SELECT * FROM TABLEA WHERE F2 = 'SITE'

This returns the record that I expect.

However: the following does not

strSQL = "SELECT * FROM TABLEA WHERE F2 <'SITE'
But if I rewrite it as:

strSQL = "SELECT * FROM TABLEA WHERE Nz(F2,'') <'SITE'

that seems to retrieve records where there may be nulls in column F2.

Let me just say that this table is a holding table for an Excel import so
values could be anything.

Is this correct. That is, I have to use the Nz function when using <>. I
haven't been doing this in some other SQL queries and now realize that I may
be missing some rows that should be returned.

Can someone explain this to me?

Thanks.
null is not comparable to anything: it is the void.

null = null equates to false
null <null will also be false
something = null will return false
something <null will likewise be false

Something + null equates to null; the only operator that breaks the
rule is the ampersand concatenation operator &

the only way to work with a null is to test for nullness, and match on
the resultant, or convert the null to some other value..
nz() is a shorthand for iif(isnull(variable),substitute,variable)

So yes, if you have nulls, but want to equate them with something else,
you have to convert them, as in the examples below

strSQL = "SELECT * FROM TABLEA WHERE F2 <'SITE" or F2 is NULL"'
strSQL = "SELECT * FROM TABLEA WHERE F2 & "" <'SITE'"
as well as your
strSQL = "SELECT * FROM TABLEA WHERE Nz(F2,'') <'SITE'"

Oct 23 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.