By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,060 Members | 1,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,060 IT Pros & Developers. It's quick & easy.

Calculation based on count of records in a query

P: 6
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!
Jan 19 '12 #1

✓ answered by 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

Expand|Select|Wrap|Line Numbers
  1. =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

Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. =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
Jan 19 '12 #2

P: 6
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.
Jan 19 '12 #3

P: 6
Okay wait, I think I figured it out. I forgot the quote marks (duh). Thanks!
Jan 19 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.