Help with a query

  • Thread starter Thread starter Robert Gillard
  • Start date Start date
R

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
 
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.
Click to expand...

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)
 
Back
Top