Filtering on Data Range

K

Katie

I am trying to set up a form that allows a user to select the records they
want to view by date range. I want the user to be able to enter the starting
date and ending date and then view only those records in that date range.

I used the [enter start date] [enter end date] option but it's not querying
my records.

What am I doing wrong?

Thank you.
 
K

kingston via AccessMonster.com

If you're using the built in menu option Records -> Filter -> Filter By Form,
the user will need to use the words Between ... And ... If you are designing
your own query based on two text box entries, validate the entries to make
sure that the dates are real and then in your query use something like:

Between [Forms]![MyFormName]![StartDate] And [Forms]![MyFormName]![EndDate]

If you're using a parameter query, use something like this as the criteria
for your date field:

Between [StartDate] And [EndDate]

However, you will run into problems if the user enters an erroneous date or
set of dates. So you may want to try and trap bad dates with IIF() functions.

I am trying to set up a form that allows a user to select the records they
want to view by date range. I want the user to be able to enter the starting
date and ending date and then view only those records in that date range.

I used the [enter start date] [enter end date] option but it's not querying
my records.

What am I doing wrong?

Thank you.
Click to expand...
 
S

scubadiver

What I have for my database is a query which lists distinct dates (not
multiple copies). Use the query as the source for two combo boxes. List all
the dates for the first combo box and, for the 2nd combo box, only list those
dates that come after the start date (cascading combo).

SELECT [dateID].[WeekID] FROM dateID WHERE
(([dateID].[WeekID]>[forms].[report options].[txtdatefrm]));

"report options" is the name of the form and "txtdatefrm" is the name of the
first combo box

By doing this, you are guaranteed to use the correct dates.

Hope this helps!
 
Top