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????

 

[Pat Hartman]

You'll need to have the user enter the date range into two fields on a form. Then refer to the form fields from the queries.

Select ...
From YourTable
Where YourDate Between Forms!YourForm!FromDate and Forms!YourForm!EndDate;

 

[legendv]

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

 

[Pat Hartman]

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.

 

[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]

 

[Pat Hartman]

You are using the same control name -- [Op Date]. There should be two different CONTROL names. You are not referencing a field name. You are referencing a FORM name and two CONTROL names.

 

[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]

 

[Pat Hartman]

Actually:

WHERE [Op Date} Between [Forms]![Date Parameters]![??] And [Forms]![Date Parameters]![??]

 

[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.....

 

[Pat Hartman]

Are you using your actual Form name? Are you using the actual control names?

 

[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.

 

[Pat Hartman]

If you read the message, it is telling you that there is some type of aggregation in the query. That would be Sum, Avg, Min, Group By, etc. Therefore, if ANY field has been aggregated, ALL fields must be aggregated. That's just the way Totals queries work

Post the query and we'll try to figure it out.

 

[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.