Form to fill data on multiple queries?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report based on a table of data detailing incident reports from my
agency. This report is generated from a series of queries that isolates data
to compare quarter to quarter totals.

Here’s the issue: the final report is generated through a series of 6 or so
queries each of which has date-limiting expressions in the criteria field.
Some of them limit the quarter in question , some the previous quarter and
some from the same quarter a year ago. Each quarter when I run the report I
have to go into each of the queries and enter the new dates manually.

Could anyone give me a broad overview as to how I could create a form that
could ask for quarter under consideration and calculate the necessary date
ranges to create the report without having to manually change the queries.

Thanks for the help,
David
 
I have a report based on a table of data detailing incident reports from my
agency. This report is generated from a series of queries that isolates data
to compare quarter to quarter totals.

Here’s the issue: the final report is generated through a series of 6 or so
queries each of which has date-limiting expressions in the criteria field.
Some of them limit the quarter in question , some the previous quarter and
some from the same quarter a year ago. Each quarter when I run the report I
have to go into each of the queries and enter the new dates manually.

Could anyone give me a broad overview as to how I could create a form that
could ask for quarter under consideration and calculate the necessary date
ranges to create the report without having to manually change the queries.
Click to expand...

Simplest would be to use a Parameter Query to enter the "quarter in
question" and then use the DateSerial function as a criterion for the
date ranges. For example, if your Form is named frmMyForm, with
textboxes txtYear and txtQuarter, you could use a criterion

BETWEEN DateSerial([Forms]![frmMyForm]![txtYear],
3*[Forms]![frmMyForm]!txtQuarter] - 2, 1) AND
DateSerial([Forms]![frmMyForm]![txtYear],
3*[Forms]![frmMyForm]!txtQuarter] + 1, 0)

to get the "quarter in question". The DateSerial function takes three
arguments - year, month, and day - so you can add or subtract one to
the year, add or subtract three to the month, etc. to view different
quarters.

John W. Vinson[MVP]
 
Back
Top