Filtering a form based on query totals query which is based on query with dates

[cowboy]

I have a subquery that gets all the Dates and each record is an ingredient that will be used in a recipe for example

Date         Amount    Ingredient
7/1/2013      3 Cups     Flour
7/3/2013      5 Cups     Flour
7/12/2013     1 Cups     Flour

Then I have a query that queries the subquery above and groups ingredients together and sums the amounts so it will have something like this

Ingredient       SumofAmount
Flour               8 Cups
Vinegar             3 Cups
Sugar               7 Cups

Now everything is working fine if I want to get the ingredient total for the whole database, but I would like to be able to filter by date.

How do I filter a form based on the 2nd query from field from its subquery?

[Dal Jeanis]

Assuming Q1 is your first query that returns (magically) records with the three fields (IngrDate IngrAmount IngrName), then here's what your second query Q2 looks like, approximately:

Q2: 
SELECT IngrName, Sum(IngrAmount) AS SumOfAmount
FROM Q1
GROUP BY IngrName;

You just need:

Q2B:  
SELECT IngrName, Sum(IngrAmount) AS SumOfAmount
FROM Q1
WHERE IngrDate BETWEEN (date1) AND (date2)
GROUP BY IngrName;

So the SQL change is trivial. The exact syntax will depend on what kind of box you put on your form to select the start and end dates, and whether you want to allow someone to have only a start date, or only an end date, or whatever. if you post the exact SQL you have, both Q1 and Q2, and the exact names of the text boxes or combo boxes that have the dates, we'll help you figure it out.

The other thing I want to verify: the word "subquery" has a specific meaning, and I think you may not mean it. A subquery is (loosely) a small section within a given query that queries a different table based on the current record of the main query.

If what you are calling your subquery is a saved query, with a name of its own, rather than a piece of a larger query, then that particular term might get you some confusing and unhelpful responses.

[cowboy]

When I was using the word subquery I was referring to Q1 which would be its own query collecting the necessary data to run Q2.

Currently my form opens using Q2 as the record source and it asks for the parameters StartDate and EndDate.
Is there a way to open the form from another form using
DoCmd.OpenForm
and have it automatically fill in the StartDate and EndDate so I am not asked for them?

[Dal Jeanis]

1) That's what I thought. I just wanted to point it out, because the term has a technical meaning that could be a gotcha if you asked a different question later.

2) Yes - google "OpenArgs" The reference page for Access 2003 is http://msdn.microsoft.com/en-us/libr...ffice.11).aspx, but there are a lot of different pages on how to use it.

[cowboy]

If you want to use OpenArgs, you have to get the arguments out with the forms Form_Open event correct? When I open the form it asks me for the parameter variables before the Form_Open event is called.

[Dal Jeanis]

Hmm. I'll have to research, but if you're right and that's the way they decide to order the events, then you have two options -

1) Take the record source off the form, and assign the record source to the form during the form open event, after you receive the openargs;

or

2) Assign the desired values to temporary variables, and change the query to use the temporary variables.
http://accessexperts.com/blog/2010/0...2007-and-2010/
http://msdn.microsoft.com/en-us/libr.../ff194564.aspx

[cowboy]

Great, I had never heard of TempVars, seems just like a global variable. Thanks for the help.

[Dal Jeanis]

Happy to oblige, cowboy.