date range OR ALL parameter query

[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

 

[Douglas J. Steele]

Try

Between Nz([start date],#1/1/100#) And Nz([end date],#12/31/9999#)

 

[John W. Vinson]

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?

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.

 

[Brian]

Many thanks to Douglas & John for their valued input. I wish I had sought
help earlier.

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?

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.