help with DLookup to display values on report 2

[laina222]

I have a report that displays the records for a certain date range entered by the user.
At the bottom of the report I want to display the Averages for certain fields in the records. I can't do this in the same query that's being used in the report because there is only one average for all records. My first query asks the user to enter the date range. My second query references the first query, which actually seems like it's not going to work because I'm not sure if the dates are going to be correctly referenced.
I learned that I need to use DLookup for this, but it's not working right so far. Perhaps I need to add criteria, which I know is optional, but to get the averages for the dates that are entered by the user, how would I do this without prompting the user a second time? If I don't use any criteria and simply use DLookup to reference the field, then it asks me for the value of "DLookup("AveragePercentage", "Query2")

 

[dhookom]

The only reason you would need to use DLookup() or DSum() or DCount() is if you want to query a recordset that is not a subset of your report's recordset. For instance, if you want to count the number of records in a report where Gender ="F", you use a text box with a control source like:
=Abs(Sum([Gender]="f"))
If this doesn't answer your question, please be a bit more specific.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[laina222]

Well that's exactly why I need to use DLookup - I need to reference a query that is not a subset of my report's recordset, as you say. The first query pulls individual records but what I need to use DLookup for is the Averages of all the records pulled in the first query. This cannot be done in the same query, because there is not an average for each record, but rather ALL of the records is what the averages are based on. I'm familiar with control sources, but that's only when the report uses one table or query and this report does not.

 

[mordja]

laina,

Im not quite sure I understand you. If you only want the averages for the records that the report is based on why not just use a textbox and calculate the averages. On the other hand if you want the average for a set that is greater than one the report is based on you will need a dlookup.

Mordja

 

[dhookom]

Your DLookup() or other domain aggregate function can use the query parameters from your report's record source. For instance if you have parameters like [Start Date] and [End Date], you can use an expression in a control source like:
[blue]
=DSum("Qty","tblSales","[SaleDate]>=#" & [Start Date] & "# AND [SaleDate]<=#" & [End Date] & "#")[/blue]

Your report might be limited to sales for John Smith but the DSum() references the entire sales table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[laina222]

Okay, I'm trying to do what Mordja suggested and create a text box to just calculate the average based on what's on the report, so why doesn't this work?

=(Sum([Percentage]))/(Count([Percentage]))

When I pull the report, those fields come out saying "error".

I also tried this, but it told me I had an extra ')' stuck in there. I don't think I do, but even if I did and removed it, I'll bet this STILL wouldn't work!

SELECT ROUND(Avg([Percentage]),1) AS AveragePercentage FROM qryPremiumTeleappReport;

 

[dhookom]

Several observations:
1) the "domain" of domain aggregate functions must not be a parameter query.
2) Are you sure your text box is not in a Page Footer section
3) If your "domain" to pull values from is different from the report's "domain" then you will need to use something like DSum()...
4) domain aggregate functions will work in page footers
5) stop using parameter prompts and start using references to controls on forms
6) DLookup() will return only the single field value from the first record it finds in a domain.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[laina222]

Duane, you said the magic words, those being "Page Footer".

Thanks all!