Multiple queries based off of the same criteria (input)

[Brett]

Hello,

Is it possible to have just one criteria and have it apply to a group
of queries?

I am trying to create a report with the separate results of 4 queries
based on a prompt for the user to input (only once) a date. Is this
something that I program into the report?

Also, this is related but may require a separate posting, but can I do
this with a list box for the user to select a choice, such as
LastMonth, LastWeek, etc.?

Thanks for your help in advance,
Brett

[Phil Stanton]

Re: Multiple queries based off of the same criteria (input)

Often pays to create a date input form with FromDate & ToDate on it. The
underlying queries for the various reports will have criteria based on these
FromDate and ToDate on the form

You can have fun designing the form to get those 2 dates from combo boxes or
whatever, but I certainly would have a command button that set the ToDate to
Now()

HTH

Phil

"Brett" <brettcbarry@gm ail.comwrote in message
news:ef170f9f-fa79-4bab-880d-d6d86e13c9d5@d1 9g2000prm.googl egroups.com...

Hello,
>
Is it possible to have just one criteria and have it apply to a group
of queries?
>
I am trying to create a report with the separate results of 4 queries
based on a prompt for the user to input (only once) a date. Is this
something that I program into the report?
>
Also, this is related but may require a separate posting, but can I do
this with a list box for the user to select a choice, such as
LastMonth, LastWeek, etc.?
>
Thanks for your help in advance,
Brett

[fredg]

Re: Multiple queries based off of the same criteria (input)

On Sun, 25 May 2008 13:07:58 -0700 (PDT), Brett wrote:

Hello,
>
Is it possible to have just one criteria and have it apply to a group
of queries?
>
I am trying to create a report with the separate results of 4 queries
based on a prompt for the user to input (only once) a date. Is this
something that I program into the report?
>
Also, this is related but may require a separate posting, but can I do
this with a list box for the user to select a choice, such as
LastMonth, LastWeek, etc.?
>
Thanks for your help in advance,
Brett

It's not clear to me if you are running 4 different reports base on 4
different queries, or 1 report based upon 4 different queries.
The below assumes 4 reports. Just modify it if it is one report.
You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the reports.

Second, create a report, using the query(ies) as it's record source,
that shows the data you wish to display for ALL records.

Next, make a new unbound form.

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate" .
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query.
As Criteria on the DateField, write:
Between forms!ParamForm !StartDate and forms!ParamForm !EndDate

Do the same for each query that is used in each of the reports.

Then on the first report that is run, code that Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

On the final report that is run, code that Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the first Report.
The report will open the form.

Enter the starting and ending dates.
Click the command button.

The first Report will display just those records selected.
Open each additional report in this series.

When the final Report closes it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Albert D. Kallal]

Re: Multiple queries based off of the same criteria (input)

>but I certainly would have a command button that set the ToDate to
Now()
>

I think you meant to use the date function in the above, want to avoid using
the now() function if you do not need it.

You can mess up your dates in a database is to start using now() as the
default for your date fields. The now function include the date + time
portion

This means virtually any query that you build to test for a date set via
now() will have to include the correct time also.
eg:

select * from tblCustomers with MembershipDate = #10/01/2007 12:43 PM"

I suspect we actually don't really quite remember what time the customer
became an active member.

So, use caution in using date() functon vs that of Now().

if your dates all have a time component attached to them, and you have to
use a time/date range to select dates in your query...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Phil Stanton]

Re: Multiple queries based off of the same criteria (input)

Culpa mia

You're absolutely right ... as usual

Phil


"Albert D. Kallal" <PleaseNOOOsPAM mkallal@msn.com wrote in message
news:Lnp_j.1649 73$rd2.62016@pd 7urf3no...

>

>>but I certainly would have a command button that set the ToDate to
>Now()
>>

>
I think you meant to use the date function in the above, want to avoid
using the now() function if you do not need it.
>
You can mess up your dates in a database is to start using now() as the
default for your date fields. The now function include the date + time
portion
>
This means virtually any query that you build to test for a date set via
now() will have to include the correct time also.
eg:
>
select * from tblCustomers with MembershipDate = #10/01/2007 12:43 PM"
>
I suspect we actually don't really quite remember what time the customer
became an active member.
>
So, use caution in using date() functon vs that of Now().
>
if your dates all have a time component attached to them, and you have to
use a time/date range to select dates in your query...
>
>
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com
>
>

[Brett Barry: Go Get Geek!]

Re: Multiple queries based off of the same criteria (input)

Thanks again for your responses. I will provide a little more
information to clarify what it is that I am trying to do. I am
creating one report with several columns that pull data from 3
different tables and are based on different date parameters. I am
pulling the data from QuickBooks via ODBC and the date format data
returned is in the format m/dd/yyyy or mm/dd/yyyy.

One column will show the sum of all line items (SalesOrderLine .Amount)
based on all sales orders with the ShipDate that contains the current
month.

Another column will show the sum for all sales orders with ShipDate
for the beginning of the following month and beyond.

Another column will show the sum of all line items
(InvoiceLine.Am ount) for all invoices with the TxnDate for the current
month.

Another column for all invoices with the TxnDate for same month last
year as the current month.

Another column for all invoices with the TxnDate from the beginning of
last year until the same day last year as the current day when the
report is ran.


I have tried using the Date() function as both a parameter or as part
of a build expression in a query, but I get an ODBC conversion error.
I have also tried using the Month() function, but it gives me 12 rows
of data, the sum for each month when I only want the current month,
Month = 6 for June, for example.

A few questions: should I be using a date prompt (From and To) for the
current month in the report? Should I be using any date parameters in
the queries or should that be done in the report itself and how is
that done?

Any resources you can provide where I can also learn how to do this,
which seems pretty complex, would be appreciated.

Brett