Calculation based on count of records in a query

[brittb]

Hi,

I'm trying to create report that will have calculated values based on the number of returned records for a query. The query returns records based on the date, so the number of records returned will change day to day. The calculations in the report should reflect the fluctuating number of records in the query. Hopefully that makes sense.

So in the report, I have a text box with the Control Source:

=13.2+4.4*Count ([QueryName].[FieldName])

Access seems to like the context okay (no error messages in Design View), but I get an error when I go to Report View. Is there some other way I should be telling it to count the number of records in the query? Is there something wrong with the syntax?

Thanks!

[Stewart Ross]

If you are trying to count records in a query which is NOT your report's recordsource you will have to use the DCount domain aggregate function to do so - you cannot use Count on a table which is not in scope within your report's recordsource.

To use DCount in your expression above the syntax would be

=13.2+4.4*DCount("*", "[YourQueryName]")

If instead you are trying to count the number of records in the report's recordsource we'd need to know whether or not the count is at a different level of grouping and what grouping you are hoping to use - if you apply Count within a detail row for instance you will normally get a value of 1 returned (as each row is individual).

-Stewart

[brittb]

Okay, I tried that and now I'm getting "#Name?" in the report view. What's wrong now? I triple-checked the name of my query; that's not the problem.

No, the query is not the report's record source. When I set the record source as this query, I get an "Enter Parameter Value" prompt, which I don't know how to get rid of.

[brittb]

Okay wait, I think I figured it out. I forgot the quote marks (duh). Thanks!