Limit Form to Date Range

  • Thread starter Thread starter Nick M via AccessMonster.com
  • Start date Start date
N

Nick M via AccessMonster.com

I have a set up a query that show the the following: site #, date, comments,
and some other information.

I have aslo created a form that will run this query based on the site #, and
if blank, show all sites. I need to add a date range to the form so that I
can select a site within a set of dates and make modifications to the data.
What criteria do I need to use to have the information from my two text boxes
(txtStartDate, txtEndDate)??

Thanks,

Nick M
 
The problem is that I don't know how you've implemented the criteria into
the rest of the query, so this may not work directly for you, but hopefully
you get the gist of how to use as a parameter.

....WHERE [DateField] Between Forms![Form Name]!txtStartDate And Forms![Form
Name]!txtEndDate
 
I have tried to add that to the criteria, but even after taking out the site
criteria, I cannot get the query to find the range. It returns blank cells.
Here is the exact text I have in the criteria for that datefield:

"WHERE [DateField]" Between [Forms]![frmSearch]![txtStartDate] And [Forms]!
[frmSearch]![txtEndDate]

Am I missing something obvious?
 
It may not be the best way, but I finally found a solution. First, I needed
to use <= AND >= to define my ranges, using the Between funtion would not
allow me to account for the possibility of an empty field.

The trick part was settin up the criteria to accomodate the 7 situations
possible given 3 different criteria. Ex. All 3 fields filled in, only the
first two, none, etc...

Its kind of cumbersome, and I don't know that would want to do it for much
more than 3 different search criteria, but it does work.
 
Sometimes, if you don't strong type the parameter as a date, it will return
no records. View / Parameters from the main menu to give each parameter a
type.
 
Back
Top