Multiple queries using one date range

[legendv]

Another question that I have pondered for awhile.

I have 6 queries that calculate different values for various results but will use the same date range. Example:


Query 1: Counts Customers in date range
Query 2: Shows most popular products for date range
Query 3: Shows most popular referral for date range
Query 4: Ratios customer to products for date range
Query 5: Ratios customer to referrals for date range
Query 6: Ratios customers to total product sales for date range.

Presently:
The user has to re -enter the date range for each query

What I want to happen:
The user to enter the date range once and the other queries to run while using that one date range.

Is it possible, if so,,,,, how????

 

[legendv]

Pat, I'm sorry, but could you elaborate more on the actual implementation of that

 

[AWirsching]

Create a form with two unbound fields and one button. Use the two fields to enter the begin date and the end date. Name the controls- txtBeginDt and txtEndDt. Then modify the 6 queries to change their criteria to reference these form field names rather than prompt.

Where SomeDate Between Forms!YourNewForm!txtBeginDt and Forms!YourNewForm!txtEndDt;

Then use the button's click event to run the 6 queries.

I'm having a similar issue, however, being a complete noob...I need further clarification. I created a simple form with Start and End Date as well as the bottom but I'm not sure how to build it correctly.
I also need further clarification on where in the query to reference the form.
Thank you!

 

[pbaldy]

The button would open the form or report the query is being used for, using OpenForm or OpenReport. The form reference would go in the criteria row of the date field.:

Between Forms!YourForm!FromDate and Forms!YourForm!EndDate

 

[AWirsching]

Thank you!!!!

 

[pbaldy]

Happy to help!

 

[pbaldy]

In response to your PM below, it's best that technical discussion takes place on the forum so that later users may benefit.

My answer is that it you should replace the items in red with your names, whatever they are. Note that any object/field name with an inadvisable space or symbol needs to be bracketed, like [Op Date].

Between Forms!YourForm!FromDate and Forms!YourForm!EndDate

Hey there,

I may have gotten excited a bit too soon...
I'm nots sure where exactly my problem lies.
If my tables use 'Op Date instead' of 'Start Date', does that make a difference? I have relationships built off of an incident number, do I have to connect 'Op Date' to 'Start Date'?

Thank you in advance!

 

[AWirsching]

So if the date field in my table (and queries) is label "Op Date" then instead of "Start Date"/"End Date", I would put "[Op Date]"?

Between [Forms]![Date Parameters]![Op Date] And [Forms]![Date Parameters]![Op Date]

 

[pbaldy]

So if the date field in my table (and queries) is label "Op Date" then instead of "Start Date"/"End Date", I would put "[Op Date]"?

Between [Forms]![Date Parameters]![Op Date] And [Forms]![Date Parameters]![Op Date]

No, I think there's confusion. In the query, under the field "Op Date" in the criteria row, you put this, using the names of the two textboxes on the form.:

Between [Forms]![Date Parameters]![Op Date] And [Forms]![Date Parameters]![Op Date]

 

[AWirsching]

Ok, so the for the Mico I have:

Where Condition: [SomeDate] Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]

 

[AWirsching]

No, I think there's confusion. In the query, under the field "Op Date" in the criteria row, you put this, using the names of the two textboxes on the form.:

Between [Forms]![Date Parameters]![Op Date] And [Forms]![Date Parameters]![Op Date]

Oh ok.....

 

[AWirsching]

The form 'Date Parameters' form is strictly for the multiple queries which will then open the report. I did just as the previous thread stated and added the two unbound fields and a button for the Macro...

Macro: Where Condition= [Op Date] Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]

In the queries I have under the 'Op Date' field: Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]

However, when I try to run the Date Parameter form, I get the message "Your query does not include the specified expression 'Op Date' as part of an aggregate function."
I get this message multiple times, so I know it has to be the queries.

 

[AWirsching]

 

[AWirsching]

I have multiple queries because I need to gather the sums of various questions. Some questions are simple yes no while others are out of a list of items, such as the one pictured above.

 

[AWirsching]

SELECT Count([Operational AAR/Summary]![Investigation Type]) AS NoKnocks, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
WHERE ((([Operational AAR/Summary].[Op Date]) Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]) AND (([Operational AAR/Summary]![Investigation Type])=7));

 

[AWirsching]

SELECT Count([Operational AAR/Summary]![Investigation Type]) AS NoKnocks, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
GROUP BY Count([Operational AAR/Summary]![Investigation Type]), [Operational AAR/Summary].[Op Date], [Operational AAR/Summary]![Investigation Type]
HAVING ((([Operational AAR/Summary].[Op Date]) Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]) AND (([Operational AAR/Summary]![Investigation Type])=7));
??

 

[AWirsching]

SELECT Count([Operational AAR/Summary]![Investigation Type]) AS NoKnocks, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
GROUP BY Count([Operational AAR/Summary]![Investigation Type]), [Operational AAR/Summary].[Op Date], [Operational AAR/Summary]![Investigation Type]
Where ((([Operational AAR/Summary].[Op Date]) Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]) AND (([Operational AAR/Summary]![Investigation Type])=7));

 

[CJ_London]

GROUP BY Count([Operational AAR/Summary]![Investigation Type]), [Operational AAR/Summary].[Op Date], [Operational AAR/Summary]![Investigation Type]

just needs to be

GROUP BY [Operational AAR/Summary].[Op Date]

and if you want to group by [Operational AAR/Summary]![Investigation Type] then you need to include it in your select part as well

 

[AWirsching]

The WHERE clause needs to go before the Group by clause.

I thought you were changing the properties on the QBE rather than editing the SQL itself.

First off, what is QBE?
I switched the Where to go before the Group By and all I did was cut and paste but now I have another error message:
SELECT Count([Operational AAR/Summary]![Investigation Type]) AS NoKnocks, [Operational AAR/Summary].[Op Date]
FROM [Operational AAR/Summary]
Where ((([Operational AAR/Summary].[Op Date]) Between [Forms]![Date Parameters]![Start Date] And [Forms]![Date Parameters]![End Date]) AND (([Operational AAR/Summary]![Investigation Type])=7));
GROUP BY Count([Operational AAR/Summary]![Investigation Type]), [Operational AAR/Summary].[Op Date], [Operational AAR/Summary]![Investigation Type]

From this query I'm trying to only pick out a specific investigation type (7) as well as by date.

 

[pbaldy]

The QBE is design view as opposed to SQL view. Remove the semi-colon at the end of the WHERE clause and see if that helps.