Single Query for several different reports

[Tommy Boy]

Hi

I have six or seven reprots I run every month. Each report referrences a
different query. Each month I go into each report query separately and enter
the criteria for "Date Issued" to be >1/1/2007 <3/1/2007 to give me January's
activity.

I would like to enter this only once (somewhere) so that each query would
then guide the reports to give me January's activity.

If you can help (please keep it simple, I'm new at this stuff)??

Tom

 

[tina]

this is a common requirement. a standard solution is to create a form (or
use an existing one if it suits your purpose), and add two unbound textbox
controls to the form, named txtStart and txtEnd. in each query's Design
view, add criteria to the date field, as

Between Forms!FormName!txtStart And Forms!FormName!txtEnd

make sure that the criteria above goes all on one line in the query grid,
regardless of possible line-wrap in this post. next, on the menu bar, click
Query | Parameters and add each parameter to a separate line, as

Forms!FormName!txtStart
Forms!FormName!txtEnd

and set the DataType for each to Date/Time.

note that your posted criteria dates

1/1/2007 <3/1/2007

will not give you January's data (even when the syntax is correct); it will
return all records with a date between 1/2/2007 and the last day of
February. the "greater than" and "less than" operators *exclude* the
comparison values from the returned data set, while the Between...And...
comparison *includes* the comparison values in the returned data set.

btw, if you're always pulling a calendar month's data, you might consider
adding calculated fields to your queries instead, as

MyYear: Year(DateFieldName)
MyMonth: Month(DateFieldName)

then add an unbound textbox control to the form, with a DefaultValue of
Year(Date()) and named txtYear, and a combo box control that lists all the
months, named cboMonth, with a RowSourceType of Values, and RowSource of

1;January;2;February;3;March....;12;December

set the control's ColumnCount to 2 and the BoundColumn to 1 and the
ColumnWidths to 0"; 1

set criteria in the MyYear and MyMonth fields in the query, respectively, as

Forms!FormName!txtYear
Forms!FormName!cboMonth

this alternate setup allows you to choose only the month (and change the
year when you need to) in the form, before running your reports - rather
than typing specific dates in two fields, which has a greater potential for
user error.

hth

 

[Tommy Boy]

Tina

It sure appears you know what your doing, sorry I don't ... so be a little
patient.

First I always do my reprots by the month, ie January, Feb etc. ...
Therefore I like your second suggestion. Lets see if I can Take it one step
at a time.

1. Create a Form (Called DATE) in form view and create two unbounded
textbox controls named "MyYear" and "MyMonth".

Here's where I start to get confused (sorry). I assume I don't need to
refer to a table or query at this point.

2. Once I get the form done (with two fields) then I open a query in design
view.

IS this ok so far?

Thanks, Tom

 

[Tommy Boy]

Tina

Upon review of your response, it looks like if I use your second option I
need to:

A. add "calculated fields?" to each of the queries?

B. add "unbound textbox control to each form"? with a DefaultValue of > >
Year(Date()) and named txtYear,

C. add a combo box control (named cboMonth) that lists all the months, with
a RowSourceType of "Values", and RowSource of

1;January;2;February;3;March....;12;December

D. set the control's ColumnCount to 2 and the BoundColumn to 1 and the
ColumnWidths to 0"; 1

E. set criteria in the MyYear and MyMonth fields in the query,
respectively, as

Forms!FormName!txtYear
Forms!FormName!cboMonth

Do I have this right? If so then all I don't know how to do is adda
calculated field to a query, I'll look this up.

Thanks Tom