Query User Defined date range

  • Thread starter Thread starter Kevbro7189
  • Start date Start date
K

Kevbro7189

I have a query with these fields [Note], [yes/no], [Number], [Date] which
drives a report. I want to build a form which will show a Start Date and End
Date that the user can change. Once those dates are accepted the Query
should look at that form and only shows the records that fall between the
Start Date, and End Date.

My problem is I can't figure out the formula for the Criteria in the query.
 
I have a query with these fields [Note], [yes/no], [Number], [Date] which
drives a report. I want to build a form which will show a Start Date and End
Date that the user can change. Once those dates are accepted the Query
should look at that form and only shows the records that fall between the
Start Date, and End Date.

My problem is I can't figure out the formula for the Criteria in the query.
Click to expand...

First off... change the name of the field Date. It's a reserved word (for the
built in Date() function) and will very likely cause problems. I'd use a form
(I'll call it YourForm) with unbound textboxes named txtStartDate and
txtEndDate.

If the table's date field contains both a date and a time... and if you want
to return all records later than Start Date if End Date is blank, all records
earlier than End Date if Start Date is blank, and all records in the table if
they're both blank, you can use a criterion
= NZ([Forms]![YourForm]![txtStartDate], #1/1/100#) AND < DateAdd("d", 1, NZ([Forms]![YourForm]![txtEndDate], #12/30/9999#))
Click to expand...
 
Back
Top