Help with a query

[Robert Gillard]

On a form (rcord) I have placed 2 text boxes (text18 and text19) which I
have designated as short dates, I have also placed a button to run a query
i.e. show all records between 01/09/2003 and 21/09/2003.

The query itself is adjusted in the date received column to

between forms!rcord!text18 and forms!rcord!text19

This works fine , however sometimes we want to see all records. If we leave
the text fields on the form blank then no records are returned. How can I
adjust the query to allow for this situation.

Bob

 

[Ken Snell]

Try this:

between Nz(forms!rcord!text18, 1) and Nz(forms!rcord!text19, 50000)

 

[John W. Vinson]

between forms!rcord!text18 and forms!rcord!text19

This works fine , however sometimes we want to see all records. If we leave
the text fields on the form blank then no records are returned. How can I
adjust the query to allow for this situation.

I'd suggest changing the control names to something meaningful.

The criterion I use for date ranges lets the user leave the start date
blank to see all records older than the end date; leave the end date
blank to see all records newer than the start date; or leave both
blank to see all records. The criterion also covers users entering
non-standard date formats (to some degree) and catches those records
with a time portion, which will be missed using a pure date criterion:

([datefield] >= DateValue(Forms!rcord!text18) OR Forms!rcord!text18 IS
NULL) AND ([datefield] < DateAdd("d", 1,
DateValue([Forms]![rcord]![text19]) OR [Forms]![rcord]![text19] IS
NULL)