Date Range selector

[Newbie11]

I've seen this on several web applications and was wondering if it were possible in Access. I would like to have a combo box on form that has fields for Last 7 days, Last 30 days, Last Quarter, This Quarter, and Specify dates ( where the user would select the beginning and ending dates from two different calendars). This would call info from a query and display it in a report.

[June7]

What do you mean by 'fields'? Do you really mean row options?

[Newbie11]

Yes, that's what I meant...sorry

[June7]

Build a RowSource for a combo or list box by querying a table or by value list. Set RowSourceType property as ValueList. Type the list into the RowSource property:
Last 7 days;Last 30 days;Last Quarter;This Quarter;Specify Date

Now write code in AfterUpdate event of the combobox to do something with the selected value.

[Newbie11]

Great call. After some tweaking, I took your advice and made a "Filter" form for parameter input to display the respective reports. I created two addtional queries - one by ID and one by Name that take the parameters from form Filter and correspond to the appropriate reports.

The only problem I am running into is where to put the date paramters. I'm not sure if this belongs in the after update property of the combo box as you suggested or the query. I have not run to parameters side by side so I'm unsure where to begin. I included the db some gist of what I'm referring to.

[June7]

I forgot about the Specify Date option.

This could get a little complicated.

The combobox AfterUpdate would have condition that it runs filter code only if the selection is not Specify Date, else make the date textbox visible and get focus. Then the AfterUpdate event of that textbox would have code to run filter with date parameter.

[Newbie11]

In the AfterUpdate event do I need code that supplies a date to date field in the ID or Name query or that opens the report itself. I'm not sure how to filter by name or id and date at the same time.

[June7]

Here is one way to set up queries that filter records with criteria entered on form. The query would be the RecordSource for report. Instead of code to open query, use the OpenReport action, note the use of LIKE operator and wildcards. http://datapigtechnologies.com/flash...earchform.html

I don't normally set up queries with input parameters and instead use VBA to construct the WHERE argument of OpenReport. However, you might find the parameterized query method easier for now.

Handling the date range can be tricky. If I remember right, wildcard will not work. When date criteria is not provided, need to use alternate dates for the range that are guaranteed to be beyond any dates possibly in the db. Example:

OrderDate BETWEEN Nz(Forms!Filter![txtStart],#1/1/1900#) AND Nz(Forms!Filter![txtEnd],#12/31/2900#)

[Newbie11]

Thanks. This works great!