Nz Function, query criteria with Between

[ghetto_banjo]

I have a form where a user can specify a variety of criteria. In my
query, I use the Nz function to look at the form fields and if they
are Null then it returns all values. That is working fine.


Now I have two text boxes on the form that are a From/To Date range.
I have If statements setup in VBA to make sure that either both boxes
are Null or both boxes are not Null.

I want my query to use that date range as criteria if it is entered,
but return all values if the dates are not entered. I can't seem to
get this logic down using an Nz function.


Thanks for the help!

 

[Brendan Reynolds]

I have a form where a user can specify a variety of criteria. In my
query, I use the Nz function to look at the form fields and if they
are Null then it returns all values. That is working fine.


Now I have two text boxes on the form that are a From/To Date range.
I have If statements setup in VBA to make sure that either both boxes
are Null or both boxes are not Null.

I want my query to use that date range as criteria if it is entered,
but return all values if the dates are not entered. I can't seem to
get this logic down using an Nz function.


Thanks for the help!

Something like this should do it ...

SELECT * FROM YourTable WHERE (YourDateField BETWEEN
Forms!YourForm!YourFromTextBox AND Forms!YourForm!YourToTextBox) OR
(Forms!YourForm!YourFromTextBox IS NULL AND Forms!YourForm!YourToTextBox IS
NULL)

 

[ghetto_banjo]

Worked like a charm. Thanks for the help Brendan.