Date Criteria with Nz()

[Question Boy]

I have the following WHERE clause in my query

WHERE (((Nz(Year([Delivery Date]),"*"))=[For which year]))

However when the user does not enter a value at the prompt, I get no records
returned and I want all the records returned (regardless of the Delivery
Date). What is wrong with my SQL statement?

Thank you,

QB

 

[Rick Brandt]

I have the following WHERE clause in my query

WHERE (((Nz(Year([Delivery Date]),"*"))=[For which year]))

However when the user does not enter a value at the prompt, I get no
records returned and I want all the records returned (regardless of
the Delivery Date). What is wrong with my SQL statement?

Thank you,

QB

Never apply criteria to an expression if there's another way as it prohibits
the use of an index.

WHERE ([Delivery Date] >= DateSerial([For which year (yyyy)], 1, 1)
AND [Delivery Date] < DateSerial([For which year (yyyy)] + 1, 1, 1))
OR [For which year (yyyy)] Is Null

As long as you enter [For which year (yyyy)] exactly the same in all three
cases you will only be prompted once. The "(yyyy)" is to remind the user to
enter a four digit year.

Also note that there are parenthesis around the two ANDed criteria to make
the OR criteria stand on its own.