SQL WHERE help

[cinnie]

hello gurus

I posted a question 4 or 5 days ago and got two excellent answers from John
Vinson (who used SQL) and Albert Kallal (who combined VBA and SQL). Both
solutions worked far better (faster and easier to implement) than what I had
been doing previously.

My problem is that I don't fully understand why John's SQL code works the
way it does. He suggests replacing the following WHERE clause:

WHERE (Z=cboZ AND R=cboR AND D=cboD) '0 nulls
OR (Z=cboZ AND R=cboR AND D is Null) 'exactly 1 null
OR (Z=cboZ AND R is Null AND D=cboD) ' "
OR (Z is Null AND R=cboR AND D=cboD) ' "
OR (Z=cboZ AND R is Null AND D is Null) 'exactly 2 nulls
OR (Z is Null AND R=cboR AND D is Null) ' "
OR (Z is Null AND R is Null AND D=cboD) ' "
OR (Z is Null AND R is Null AND D is Null) '3 nulls

with...

WHERE NZ([z], [cboZ]) = cboZ
AND NZ([R], [cboR]) = cboR
AND NZ([D], [cboD]) = cboD

This works perfectly, but as a newcomer to code, I can't see why. What
exactly does 'NZ([z], [cboZ]) = cboZ' mean? If [z] is null, assign
it the value of [cboZ], but how do I interpret the '= cboZ'?

Sorry if this is a dumb question

 

[John W. Vinson]

WHERE NZ([z], [cboZ]) = cboZ
AND NZ([R], [cboR]) = cboR
AND NZ([D], [cboD]) = cboD

This works perfectly, but as a newcomer to code, I can't see why. What
exactly does 'NZ([z], [cboZ]) = cboZ' mean? If [z] is null, assign
it the value of [cboZ], but how do I interpret the '= cboZ'?

The NZ function returns its first argument if it is not NULL, and its second
argument if the first argument *is* null. The second argument is optional but
handy.

In this case if the field Z is null; the NZ function will return the value of
the combo box cboZ. Since you're comparing the value to cboZ, it will of
course match.

So... if Z is null, return True; if it's not null, then it must match cboZ.

Same for the other fields.

 

[Sylvain Lafontaine]

Your WHERE statement can be rewritten as:

WHERE (Z is Null or Z = cboZ)
And (R is Null or R = cboR)
And (D is Null or D = cboD)

As for "NZ([z], [cboZ])", this is a function returning a value, not an
assignation: it returns the value of [cboZ] if [z] is Null, otherwise it
returns the value of [z].

By using this function, the above expression can be simplified: when [z] is
not Null, we get the test for « Z = cboZ »; otherwise, when [z] is Null, we
get: « cboZ = cboZ »; which is always true. So, writing NZ([z], [cboZ]) =
cboZ is another, similar way of expressing the above WHERE statement.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)