Fill Report criteria

[zrobinso]

I have a report that uses 5 sub reports and is based on 5 seperate querys. Each query is linked to a query [qryDates] that asks [startdate] and [enddate]. When I run the main report it asks for start and end date 5 times before report is displayed.

How can I get it to ask only once without editing query each time. I am using a switchbord to keep uses from seeing database.

 

[LarryDeLaruelle]

Z:

You need to set up a query by form to prompt for the dates and, in the query's criteria, reference the form.

Create an unbound form with three controls: two text boxes and one command button.

The text boxes will accept the user's input for start and end dates the command button will open the report.

For this example, we'll call the form frmDates and the text controls txtStart and txtEnd.

In each of the queries where the date is referenced replace your prompts with this code:

>= Forms!frmDates.txtStart and <= Forms!frmDates.txtEnd

When the report is opened, it will open each of the source queries. When the queries based on dates are opened, they will look to frmDates to get the values necessary for the date range.

You might want to set some error checking for empty text boxes, invalid dates, etc.

Sounds like circular logic, but it will work.
Larry De Laruelle
larry1de@yahoo.com