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

The Dreaded DCOUNT Function in a Report

P: 17
Good afternoon,

I am attempting to count only those records within a report, which is based on a query, where Status is equal to Closed. I have tried multiple variations of DCOUNT but am continuously receiving the "#ERROR" message in the control source box on the report. Below is the expression that I have tried to use and modify numerous times. Don't know if i'm just simply having syntax issues or my formula is just wrong. I did attempt to use a suggested SQL statement which is also below but I don't know how to get the result of that SQL statement embedded in my Report. Any advice is greatly appreciated. THANKS!

My proposed statement:
Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]","ROA_Imaging_QueWeb_LINKED_Query","[Status] = 'Closed' ")
My proposed SQL statement:
Expand|Select|Wrap|Line Numbers
  1. SELECT Status, COUNT(*)
  2. FROM ROA Imaging QueWeb - LINKED
  3. WHERE Status = "Closed"
  4. GROUP BY Status;
Oct 4 '07 #1
Share this Question
Share on Google+
36 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Welcome to TheScripts.

The SQL has embedded spaces in the query name without the [] delimiters.
The name of the dataset differs between the SQL & the expression.
Try resolving these issues and seeing where that gets you.
Oct 5 '07 #2

JConsulting
Expert 100+
P: 603
Good afternoon,

I am attempting to count only those records within a report, which is based on a query, where Status is equal to Closed. I have tried multiple variations of DCOUNT but am continuously receiving the "#ERROR" message in the control source box on the report. Below is the expression that I have tried to use and modify numerous times. Don't know if i'm just simply having syntax issues or my formula is just wrong. I did attempt to use a suggested SQL statement which is also below but I don't know how to get the result of that SQL statement embedded in my Report. Any advice is greatly appreciated. THANKS!

My proposed statement:
Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]","ROA_Imaging_QueWeb_LINKED_Query","[Status] = 'Closed' ")
My proposed SQL statement:
Expand|Select|Wrap|Line Numbers
  1. SELECT Status, COUNT(*)
  2. FROM ROA Imaging QueWeb - LINKED
  3. WHERE Status = "Closed"
  4. GROUP BY Status;
Use your proposed statement in the On_Format event of the report for the appropriate section.
Expand|Select|Wrap|Line Numbers
  1. me.mytextbox = nz(DCount("[Status]","ROA_Imaging_QueWeb_LINKED_Query","[Status] Like 'Closed' "),0)
  2.  
J
Oct 5 '07 #3

P: 17
Thanks for your quick assistance. And you're right, they were different. I have been doing "trial and error" quite a bit and forgot to change the SQL statement back to using the query. If I indeed have the below now corrected with formatting, my goal is really to only use one means of putting the total on the finished report. Whether it be using the embedded SQL statement, which I'm not sure how to even embedd the statement within a report, or use the "Dcount" option as a control source with a text box. Of course at this point neither is working for me...HA! How does they look now? Thanks for your assistance.

Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]","ROA_Imaging_QueWeb_LINKED_Query","[Status] = 'Closed' ")
Expand|Select|Wrap|Line Numbers
  1. SELECT Status,COUNT(*)
  2. FROM ROA_Imaging_QueWeb_LINKED_Query
  3. WHERE Status = "Closed"
  4. GROUP BY Status;
Oct 5 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
The first one (=DCount(...)) looks fine. As we have no access to what's in the query it's hard to know if it all works.
Also, as we have no understanding of where you've tried to add this already, we don't know what you have in your report.
It would typically go in a control within the Report Header (if that helps).

PS. Please try to use the [ CODE ] tags when posting code in here. It is required and is also much easier to read - therefore easier to help you.
Oct 5 '07 #5

P: 17
Sorry about that. Just eager to get this thing resolved. I've now decided to NOT use the query as my control. I was only using a query instead of the actual table with all of the data because the query already had filtered out the needed dates. Therefore, if I now use the table instead of the query as my control source, I will need to add another condition to the DCOUNT function to contain the dates.

Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]","ROAImagingQueWeb-LINKED","([Status]='Closed') AND ([DateReported] Between #09/16/07# AND #09/30/07#)")
However though, I also have two user supplied date fields within the report that come from the Record Source (which these fields feed the query to bring out only those records with the user-defined date constraints). In other words, when a user selects to run this report, it immediately prompts them for the Start Date and End Date which is then added to the query to spit back only the records between those dates. All that to say, is there even to way have this user-supplied info added into the DCOUNT formula, using the same dates that are entered as a result of the Record Source already being the Date Query. Perhaps something like this:

Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]","ROAImagingQueWeb-LINKED","([Status]='Closed') AND ([DateReported] Between #09/16/07# AND #09/30/07#)")
Or

Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]","ROAImagingQueWeb-LINKED","([Status]='Closed') AND ([Date Reported] Between '[Enter Period Start Date]' AND '[Enter Period End Date]')")
Oct 5 '07 #6

NeoPa
Expert Mod 15k+
P: 31,494
No problem - just a reminder about the tags :)
I need to know where you have access to the values entered by the operator. How and where are they stored and where is the formula going in your report?
Oct 5 '07 #7

P: 17
The operator-entered values are a result of the query which is the Report Source of the Report. The SQL View of that query is below.

Expand|Select|Wrap|Line Numbers
  1. SELECT [ROAImagingQueWeb-LINKED].[Reference #], [ROAImagingQueWeb-LINKED].[Date Reported], [ROAImagingQueWeb-LINKED].Customer, [ROAImagingQueWeb-LINKED].Status, [ROAImagingQueWeb-LINKED].Priority, [ROAImagingQueWeb-LINKED].PROBLEM, [ROAImagingQueWeb-LINKED].Owner
  2. FROM [ROAImagingQueWeb-LINKED]
  3. WHERE ((([ROAImagingQueWeb-LINKED].[Date Reported]) Between [Enter Period Start Date] And [Enter Period End Date]))
  4. ORDER BY [ROAImagingQueWeb-LINKED].[Date Reported];
  5.  
The 'Enter Period Start Date' and 'Enter Period End Date' are then user-supplied when the query is run against the main table, ROAImagingQueWeb-LINKED, which is a linked table. Thus, because the Report Source is the query, when the report is run, the operator is prompted for the date constraints.

The Report then displays records from the table within those dates and performs various "counts" within the groups, by Status and by Priority. However, the issue is that the COUNT(*) function is of course counting all of the records within a particular group as it's designed to do. And in my case, it groups all of the "Tickets" (which the Main table is just Help Desk Ticket info) whether they be "Closed" or "Opened" and gives me a combined total of "Open" and "Closed" tickets. However, my goal is to add another text box that contains a count of just the "Closed" tickets.

I wonder though if because my Record Source for the Report is the Table, but my Dcount function is trying to use the Query that pullls from that Table makes a difference?
Oct 5 '07 #8

P: 17
Hi again folks,

Just hoping that perhaps someone could offer a few more words of advice. I definitely appreciate the help.

Brian
Oct 8 '07 #9

NeoPa
Expert Mod 15k+
P: 31,494
Brian,

Fine to bump - I must have managed to miss this over the weekend.
However, I'm probably too short of time atm to look into this deeply but a point to ponder would be that the dates entered by the operator into the running SQL (as prompted) are not available to your code. This is one of the limitations of doing it that way.
There are solutions to this but typically they depend heavily on EXACTLY what you're trying to do. That means precision of communication becomes extra important.
I'll leave that with you for now. Let me know if you have any thoughts and I will look at it again anyway if I get some more time.
Oct 8 '07 #10

P: 17
The saga continues...I've read through the FAQ in attempts to post an attachment. However, I don't believe that I have "rights" to view the "browse" button that may or may not appear when replying to a post. Is this a feature that Admin needs to activate for my profile? Thanks again for you guys continuing patience.
Oct 8 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
Brian,

I missed this post again yesterday :(
What you need to do is :
  1. Create a post (for attaching the file to).
  2. Within the post's edit time-period, edit the post and choose Manage Attachments.
  3. This brings up a separate page which enables you to browse for your file (You can alternatively type the full path into the box).
  4. Click on Upload to upload your file.
That should do you.
Let us know how you get on.
Oct 10 '07 #12

P: 17
This is a post so that I can attempt to attach the Db that I'm attemping to use the DCOUNT in from an earlier submission. The Report that I'm trying to is "ROA Imaging QueWeb Report by Resource (Sort by Priority)". You'll see the #Error in the Closed Tickets Field. This is where I'm obviously stuck and out of ideas. Thanks so much for you guys' continued help (and patience).

Brian
Attached Files
File Type: zip Imaging.zip (53.8 KB, 138 views)
Oct 10 '07 #13

P: 17
The new post is up. Thanks for the heads up.
Oct 10 '07 #14

missinglinq
Expert 2.5K+
P: 3,532
Just to make things simple I've mergerd the two posts!

Welcome to The Scripts, Brian!

Linq ;0)>
Oct 10 '07 #15

NeoPa
Expert Mod 15k+
P: 31,494
Right Brian.

I'll need to look at this at home and that rules out tonight I'm afraid (as I'm out for hours).
Bump up the thread on Friday if I manage to overlook it again will you please.
Oct 10 '07 #16

FishVal
Expert 2.5K+
P: 2,653
Hi, Brian.

The mdb you've provided makes little sense without linked Excel worksheet.

The record source for report control
Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]","ROAImagingQueWeb-LINKED","([Status]='Closed') AND ([DateReported] Between #09/16/07# AND #09/30/07#)")
seems to be Ok.

I can only guess that field name(s) may be misspelled or data type(s) may be incorrect.

You should provide linked Excel worksheet with several records sufficient for debug purposes. Replace all secure info with dummy values of the same data type.

Kind regards,
Fish
Oct 10 '07 #17

NeoPa
Expert Mod 15k+
P: 31,494
Brian, I get that you're trying to run a DCount() to populate a control somewhere and that you want the date range to be included. This range should be as specified by the operator when they run the report and enter the parameters for the underlying query.
  1. Is the control within the report itself?
  2. If so, why use DCount() (a Domain Aggregate function) as there are aggregation functions available to report controls anyway?
If you feel you do still need to use a separately defined DCount() call then the usual way of doing this is to create a form with controls on for the parameters. These controls are then referenced both in your underlying query and within the DCount() code (wherever that is).
Does this make sense?
Oct 11 '07 #18

P: 17
Well, if I understand you correctly, if the desired control is a result of evaluating the control data source of the report itself, then I'd have to say not exactly. The report is based on a query that requires the operator to provide the date range. In turn, said query looks at the linked table, "ROAImagingQueWeb-LINKED" which I thought I defined in the DCOUNT function. Which sounds like that may be my issue if I'm trying to look at a different entity (the table) but the report's source data is not the table but the query, which queries that same source table. Sheesh, that's confusing.

BUT it sounds like if I change the source data for the report to be the table itself, then I may not have to use the DCOUNT function at all, that I could use an aggregate function?? in the desired Report control (the CLOSED TICKET control in the Owner Footer group) to calculate the number of closed tickets per Owner. Although, if we change the source data to be the table rather than query, then I'm afraid I'd lose the ability to only parse out the data for an operator-supplied date range (which was the only point of the query anyway) within the Report. Unless there's another way to prompt for user input when the report is executed. Which do you think would be the best way?

And in response to "Fish", if it'd be easier for me to attach the linked spreadsheet, I'd be glad to. I just didn't want to create another post just to do an attachment.

AND THANK YOU GUYS for helping me still.
Oct 11 '07 #19

NeoPa
Expert Mod 15k+
P: 31,494
Brian,

Your post covers a lot of points so what I'll do is reply within your quoted post.
Well, if I understand you correctly, if the desired control is a result of evaluating the control data source of the report itself, then I'd have to say not exactly. The report is based on a query that requires the operator to provide the date range. In turn, said query looks at the linked table, "ROAImagingQueWeb-LINKED" which I thought I defined in the DCOUNT function. Which sounds like that may be my issue if I'm trying to look at a different entity (the table) but the report's source data is not the table but the query, which queries that same source table. Sheesh, that's confusing.
In fact the use of DCount() (or other domain aggregate functions) are MORE appropriate when dealing with recordset objects OTHER than the one which is the record source of the report. If this is the case, then it may well be appropriate to use it here. However, read on, as I think your best solution is to use a form.
BUT it sounds like if I change the source data for the report to be the table itself, then I may not have to use the DCOUNT function at all, that I could use an aggregate function?? in the desired Report control (the CLOSED TICKET control in the Owner Footer group) to calculate the number of closed tickets per Owner. Although, if we change the source data to be the table rather than query, then I'm afraid I'd lose the ability to only parse out the data for an operator-supplied date range (which was the only point of the query anyway) within the Report. Unless there's another way to prompt for user input when the report is executed. Which do you think would be the best way?
If you want to access data (say for other date ranges) within your report then you can't rely on aggregate functions within the report itself. However, if you are counting records which are processed within the report you can put "=Count([SomeControl])" in a header section (Group header or report header) which will do the job. You should understand that "[SomeControl]" means that is accessing a control in your report and not the underlying recordset directly. Only those items included as controls can be accessed like this.
If this is not appropriate for you then I would go with the form option. whereby you define the controls on the form and let it open the report for you (You'd need to code that in but we can help there if you choose that route).
And in response to "Fish", if it'd be easier for me to attach the linked spreadsheet, I'd be glad to. I just didn't want to create another post just to do an attachment.
Well, ... you could simply have attached it to this one. Tell you what, just attach it to the next one that you post. That'll be fine.
AND THANK YOU GUYS for helping me still.
No problems. Enjoy TheScripts :)
Oct 12 '07 #20

P: 17
After a longer absence than anticipated, I'm finally back trying to figure this thing out. I'm re-posting because even when I reply to the Original Post, I don't have the option to attach files. And this time the attached zipped database is not using the linked table as a source. At least the report in question isn't. I would still greatly appreciate any advice. And please feel free to combine this with the original post, like I said I wasn't given an option to attach files anymore. THANKS!!!

The Report in question is : "ROA Imaging QueWeb Eval Period Report (TEST COPY)"

You'll see under the Owner Footer that there are two controls where I have attempted to use the Count function to report how many Tickets for that Owner have a status of "Closed" and how many Tickets for that Owner have a Status of "Owned". That's where I'm having the difficulty. My goal is to have Access count and display the number of Closed vs. Owned tickets by Owner.

Also of importance, the underlying query of the report does prompt the operator for a beginning and end date but this might be causing a problem too because instead of using the Table itself (ROAImagingQueWeb-NOT LINKED"), I'm using a query so that my resulting data is limited based on user input for a date range. Maybe I just need to change the source data to be the table and not the query but then I'm stuck when I run the report because the report has to be date specific which is why I'm using the query to begin with.

I've tried so many combinations of formulas including those below, attempting to use the DCOUNT function as well.

Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]","Closed Query","[Owner]='"  & Owner & '")
  2.  
  3.  
  4. =DCount("[Status]","Closed Query","[Owner]=' & Owner & _'")
Again, thanks so much for your help.

BGM
Attached Files
File Type: zip Imaging.zip (86.2 KB, 97 views)
Oct 24 '07 #21

P: 17
Started another post to add Attachment.

The Dreaded DCOUNT Function in a Report ADDENDUM
Oct 24 '07 #22

nico5038
Expert 2.5K+
P: 3,072
Did you try:

Expand|Select|Wrap|Line Numbers
  1. =DCount("*","[Closed Query]","Status='Closed' and [Owner]='"  & [Owner] & "'")
  2.  
Because of spaces the [ and ] are needed. Best not to use spaces in (field)names :-)

Nic;o)
Oct 25 '07 #23

P: 17
Thanks for the pointer but alas it still didn't work. It gives a TOTAL of "0", which I'm not sure where it gets that number. Hmmmm. Thanks for the try though.
Oct 25 '07 #24

NeoPa
Expert Mod 15k+
P: 31,494
You needn't have started a new THREAD for this. It is possible to add an attachment to any of your posts using the Edit facility. Once in the POST edit screen you have attachment options.
Having read the first post of your new thread I'll merge it into this one for you now.
Oct 25 '07 #25

nico5038
Expert 2.5K+
P: 3,072
Are you sure that the Status field holds "Closed" and isn't a lookup field (see field in tabledefinition) holding another (e.g. numeric?) value...

Nic;o)
Oct 25 '07 #26

P: 17
Yep, it's just a text field in the original table.

And in regards to the new thread, I don't think I have the option to add an attachment just because the EDIT option doesn't appear for me on any of old posts except for a brief time right after I create a new thread. Otherwise, the only option buttons I see are "REPLY". Only when I create a new thread and then for a short time afterwards do I see the "EDIT" option which does allow me to add an attachment. For instance, even on this original thread, nowhere do I see an EDIT option on any of my posts, just REPLY. I'm not trying to disagree, I just don't have that visibility it appears. But I do appreciate the merge and the assistance.
Oct 25 '07 #27

nico5038
Expert 2.5K+
P: 3,072
The Edit/Reply shows at the bottom right of your comment. You'll have to press this after adding the comment te be able to add an attachment.

The line:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","[Closed Query]","Status='Closed' and [Owner]='"  & [Owner] & "'")
  2.  
should work, unless your Owner is a numeric ID, then use:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","[Closed Query]","Status='Closed' and [Owner]="  & [Owner])
  2.  
And make sure both Status and Owner are fields in the [Closed Query]

Nic;o)
Oct 25 '07 #28

nico5038
Expert 2.5K+
P: 3,072
OK, checked the attached database and found that the Dcount does work, but that your first Owner is Null.
This requires special coding, try:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","ROAImagingQueWeb-LINKED","[Status]='Closed' and nz([Owner],'')='" & [Owner] & "'")
  2.  
Nic;o)
Oct 25 '07 #29

P: 17
Well, I think we're getting closer but I'm still getting odd results and don't know how it's calculating the number.

For instance, if you run the Report "ROA Imaging QueWeb Eval Period Report (TEST COPY)" and use a start date of 05/01/07 and an End Date of 08/31/07, you'll see the report period is for that date range only, which that part seems to work. However, if you are look at one of the "Owners", we'll use Mike Eastham for an example in this case. My goal is not only to report how TOTAL (including Owned and Closed) tickets but also how many "Closed" tickets he had during that time. Currently, with the most recent formula suggested, it's showing that Mike had 90 Closed tickets but if you look at the report you can see that he had only 65 tickets. So, i'm not sure where this number is coming from at this point. What do you think? I attached a snapshot of the report.
Attached Files
File Type: zip ForForum.zip (16.8 KB, 94 views)
Oct 25 '07 #30

P: 17
Upon further investigation, I discovered that the function is getting the total number of Closed tickets for Mike from the TABLE itself which is what is stated as the source. So, the function is working correctly. The only issue now is that I was hoping that I could use the Report Source which is the query that prompts the user for a date range. And I thought it would be just matter of simply changing it from the TABLE to the QUERY "ROA_Imaging_QueWeb_LINKED_Query" but that didn't work. Now I get a #ERROR. :(

Getting closer though.
Oct 25 '07 #31

nico5038
Expert 2.5K+
P: 3,072
Sorry, I used the table instead of the report query, change "ROAImagingQueWeb-LINKED" into the query the report is based on.

Nic;o)
Oct 25 '07 #32

nico5038
Expert 2.5K+
P: 3,072
Hmm, lets try differently:
Expand|Select|Wrap|Line Numbers
  1. =Sum("IIF([Status]='Closed',1,0)")
  2.  
Nic;o)
Oct 25 '07 #33

P: 17
YOU MY FRIEND ARE INGENIUS. That worked great. I will need to do some homework on that one. Thank you so much.
Oct 25 '07 #34

nico5038
Expert 2.5K+
P: 3,072
Have been "fighting" with these Access "features" for years <LOL>

Success with your application !

Nic;o)
Oct 25 '07 #35

P: 17
Indeed. Thanks again to you and NeoPa for your help with this. "The Scripts" has now made it to my Bookmark list for permanent residency.
Oct 25 '07 #36

P: 1
nico5038,

I am just writing to thank you for this great alternative to DCount. I'm using Dcount the exact same way in two databases to calculate a control on the reports. It works on one and on the other one it gives "#Error." I am extremely curious to know whatever is different between the two databases, however, your solution solves the problem so I'm glad and may god bless you for sharing your knowledge and time.

Thank You
Suri!!
Jun 4 '10 #37

Post your reply

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