473,320 Members | 1,863 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Calculation based on count of records in a query

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

3 1972
Stewart Ross
2,545 Expert Mod 2GB
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
brittb
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
brittb
6
Okay wait, I think I figured it out. I forgot the quote marks (duh). Thanks!
Jan 19 '12 #4

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

Similar topics

3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
1
by: John M | last post by:
Hi, I have two incomplete lists of staff which combined create a full list of staff with duplicates. I wish to create a full list with no duplicates. Each member of staff has and obviously a...
3
by: Melissa | last post by:
I have a sales report where the sales are grouped by district. The report is based on a query with criteria for sales date to get the sales in any specified date range. I have code in the Format...
4
by: alltechsolutions.net | last post by:
Been at this for two days now... I have a customer database with various fields, two of which are: ContactID (an autonumber field) & ReferralID (long Integer) When a customer refers someone...
3
by: Paulo | last post by:
Hi everyone and thanks in advance for helping me. I´ve a form (continuous form) based on a query, wich several data fields are limited for severel fields (unbound text fields) build in footer...
1
by: aps786 | last post by:
Hi, There is a table where I store ipaddress and user who logged in from that IP. I have a query to findout all ipaddresses, from where diff users had made request. stat ------------ ip...
10
by: 60325 | last post by:
This is the page where I collect the data in drop-down boxes with values of 1-10 and send it to a submitted page to do calculations. Example: Employee1 TeamScore(1-10) Employee2 ...
5
by: Soccer5 | last post by:
Trying to Count records on a report that meet a certain criteria. Have a text box in the Report Footer that has the following in the Control Source: =Count(="S") This does not work. It...
18
by: shonam | last post by:
Hello I've got a form which is based on a query It's opened by a command button - when the command button is clicked a tect box prompts for the criteria "enter word" The query searches a...
13
by: shreyansghia | last post by:
Hello , I am using MS Access 2007. Now if i ve a subform based on a table, filtering the subform is extremely easy . I only ve to click the relevant column head on the subform and check/uncheck...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.