report on query on query - need to filter inner query?

[crabback]

Hi all!
My report looks at a companys risks in different currencies listed individually in rows. on each row a risk is evaluated as current risk and potential risk in local currency and then converted to euro and shown as current and potential risks in euro.
The report should be viewable as a monthly, quarterly and annual report.
The data used to reach the calculation for total risk and potential risk is taken from 3 tables. Each of these tables has a LastUpdated date field.
I used 2 queries to build the report - the first vwMPEC is a union query that pulls each risk amount, currency & LastUpdated, the second query qryMPEC is run on the vwMPEC and Currency table. It does 2 things: it is a total query so it groups by Currency and sums Risk -lastupdated is omitted-, and it retrieves the xchngRte from the currency table.
This all works fine. But now I need to think about the date filter for the report? How do I implement this? ...
I think I should be looking at using a datepart() function - can I use this in a query? or a dlookup?
But I keep hitting a wall with the fact that the query I want to filter is the union query which is sort of buried. I'm a little confused with this, I keep thinking it must be possible because it seems like it should be a fairly common issue. But maybe I'm looking at it the wrong way?
Please can anyone help, even point me in the right direction or tell me I'm crazy????????

Thanks

Crabback

 

[dhookom]

You should be able to create your filter criteria in your second query since it includes your date field. You should use controls on a form to provide your criteria values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[crabback]

yep I had figured that bit out but I'm stuck passing the criteria values. This is the SQL for the query the report uses:
SELECT Sum(vwMPECurrency.TE) AS TE, Sum(vwMPECurrency.MPE) AS MPE, vwMPECurrency.Currency, Currency.xchngRte AS xchngRte, Currency.xchngDte
FROM vwMPECurrency INNER JOIN [Currency] ON vwMPECurrency.Currency = Currency.ISOCode
GROUP BY vwMPECurrency.Currency, Currency.xchngRte, Currency.xchngDte, DatePart([period],vwMPECurrency.dte)
HAVING (((DatePart([period],[vwMPECurrency].[dte]))=DatePart([period],Now())));

At moment when I open the report I am prompted my the parameter [period] - I can enter yyyy for year q for quarter and m for month. How do I pass these values from form controls?

I saw a post from January where this was explained using docmd.openreport in vb but that passes a where clause and I'm using Having - I been playing around with it but i can't work it out.

Thanks Duane


Crabback

 

[crabback]

I got it to work. took a bit of twiddling but
thread703-1325875 was very helpful.

Crabback