date range OR ALL parameter query

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

OK, I've spent enough time trying to get this query to work. Time to call in
the experts.

I want a parameter query inviting the user to select records by a date range
OR selcet ALL records by leaving the date dialogue box blank.

I thought this would work but it does not -
Between Nz([start date],0) And Nz([end date],0) - No records are returned.

....my error is?

Brian
 
OK, I've spent enough time trying to get this query to work. Time to call in
the experts.

I want a parameter query inviting the user to select records by a date range
OR selcet ALL records by leaving the date dialogue box blank.

I thought this would work but it does not -
Between Nz([start date],0) And Nz([end date],0) - No records are returned.

...my error is?
Click to expand...

Using 0. A 0 value in a date/time field is #12/30/1899 00:00:00# so your
BETWEEN clause will find all records with that exact time. Douglas' suggestion
uses the minimum and maximum allowed date/time values instead.
 
Many thanks to Douglas & John for their valued input. I wish I had sought
help earlier.

John W. Vinson said:
OK, I've spent enough time trying to get this query to work. Time to call in
the experts.

I want a parameter query inviting the user to select records by a date range
OR selcet ALL records by leaving the date dialogue box blank.

I thought this would work but it does not -
Between Nz([start date],0) And Nz([end date],0) - No records are returned.

...my error is?
Click to expand...

Using 0. A 0 value in a date/time field is #12/30/1899 00:00:00# so your
BETWEEN clause will find all records with that exact time. Douglas' suggestion
uses the minimum and maximum allowed date/time values instead.
Click to expand...
 
Back
Top