The Dreaded DCOUNT Function in a Report | Newbie | | Join Date: Oct 2007
Posts: 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: - =DCount("[Status]","ROA_Imaging_QueWeb_LINKED_Query","[Status] = 'Closed' ")
My proposed SQL statement: - SELECT Status, COUNT(*)
-
FROM ROA Imaging QueWeb - LINKED
-
WHERE Status = "Closed"
-
GROUP BY Status;
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: The Dreaded DCOUNT Function in a Report
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.
|  | Expert | | Join Date: Apr 2007 Location: Houston
Posts: 601
| | | re: The Dreaded DCOUNT Function in a Report
Quote:
Originally Posted by bmyers 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:
- =DCount("[Status]","ROA_Imaging_QueWeb_LINKED_Query","[Status] = 'Closed' ")
My proposed SQL statement: - SELECT Status, COUNT(*)
-
FROM ROA Imaging QueWeb - LINKED
-
WHERE Status = "Closed"
-
GROUP BY Status;
Use your proposed statement in the On_Format event of the report for the appropriate section. -
me.mytextbox = nz(DCount("[Status]","ROA_Imaging_QueWeb_LINKED_Query","[Status] Like 'Closed' "),0)
-
J
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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. - =DCount("[Status]","ROA_Imaging_QueWeb_LINKED_Query","[Status] = 'Closed' ")
- SELECT Status,COUNT(*)
-
FROM ROA_Imaging_QueWeb_LINKED_Query
-
WHERE Status = "Closed"
-
GROUP BY Status;
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: The Dreaded DCOUNT Function in a Report
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.
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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. -
=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: -
=DCount("[Status]","ROAImagingQueWeb-LINKED","([Status]='Closed') AND ([DateReported] Between #09/16/07# AND #09/30/07#)")
Or -
=DCount("[Status]","ROAImagingQueWeb-LINKED","([Status]='Closed') AND ([Date Reported] Between '[Enter Period Start Date]' AND '[Enter Period End Date]')")
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: The Dreaded DCOUNT Function in a Report
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?
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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. -
SELECT [ROAImagingQueWeb-LINKED].[Reference #], [ROAImagingQueWeb-LINKED].[Date Reported], [ROAImagingQueWeb-LINKED].Customer, [ROAImagingQueWeb-LINKED].Status, [ROAImagingQueWeb-LINKED].Priority, [ROAImagingQueWeb-LINKED].PROBLEM, [ROAImagingQueWeb-LINKED].Owner
-
FROM [ROAImagingQueWeb-LINKED]
-
WHERE ((([ROAImagingQueWeb-LINKED].[Date Reported]) Between [Enter Period Start Date] And [Enter Period End Date]))
-
ORDER BY [ROAImagingQueWeb-LINKED].[Date Reported];
-
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?
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
Hi again folks,
Just hoping that perhaps someone could offer a few more words of advice. I definitely appreciate the help.
Brian
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: The Dreaded DCOUNT Function in a Report
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.
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: The Dreaded DCOUNT Function in a Report
Brian,
I missed this post again yesterday :(
What you need to do is : - Create a post (for attaching the file to).
- Within the post's edit time-period, edit the post and choose Manage Attachments.
- This brings up a separate page which enables you to browse for your file (You can alternatively type the full path into the box).
- Click on Upload to upload your file.
That should do you.
Let us know how you get on.
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
The new post is up. Thanks for the heads up.
|  | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 2,997
| | | re: The Dreaded DCOUNT Function in a Report
Just to make things simple I've mergerd the two posts!
Welcome to The Scripts, Brian!
Linq ;0)> | | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: The Dreaded DCOUNT Function in a Report
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.
|  | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: The Dreaded DCOUNT Function in a Report
Hi, Brian.
The mdb you've provided makes little sense without linked Excel worksheet.
The record source for report control -
=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
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: The Dreaded DCOUNT Function in a Report
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. - Is the control within the report itself?
- 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?
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: The Dreaded DCOUNT Function in a Report
Brian,
Your post covers a lot of points so what I'll do is reply within your quoted post. Quote:
Originally Posted by bmyers 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. Quote:
Originally Posted by bmyers 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). Quote:
Originally Posted by bmyers 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. Quote:
Originally Posted by bmyers AND THANK YOU GUYS for helping me still. No problems. Enjoy TheScripts :)
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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. - =DCount("[Status]","Closed Query","[Owner]='" & Owner & '")
-
-
-
=DCount("[Status]","Closed Query","[Owner]=' & Owner & _'")
Again, thanks so much for your help.
BGM
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
|  | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: The Dreaded DCOUNT Function in a Report
Did you try: - =DCount("*","[Closed Query]","Status='Closed' and [Owner]='" & [Owner] & "'")
-
Because of spaces the [ and ] are needed. Best not to use spaces in (field)names :-)
Nic;o)
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: The Dreaded DCOUNT Function in a Report
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.
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: The Dreaded DCOUNT Function in a Report
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)
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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.
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: The Dreaded DCOUNT Function in a Report
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: -
=DCount("*","[Closed Query]","Status='Closed' and [Owner]='" & [Owner] & "'")
-
should work, unless your Owner is a numeric ID, then use: -
=DCount("*","[Closed Query]","Status='Closed' and [Owner]=" & [Owner])
-
And make sure both Status and Owner are fields in the [Closed Query]
Nic;o)
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: The Dreaded DCOUNT Function in a Report
OK, checked the attached database and found that the Dcount does work, but that your first Owner is Null.
This requires special coding, try: -
=DCount("*","ROAImagingQueWeb-LINKED","[Status]='Closed' and nz([Owner],'')='" & [Owner] & "'")
-
Nic;o)
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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.
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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.
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: The Dreaded DCOUNT Function in a Report
Sorry, I used the table instead of the report query, change "ROAImagingQueWeb-LINKED" into the query the report is based on.
Nic;o)
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: The Dreaded DCOUNT Function in a Report
Hmm, lets try differently: -
=Sum("IIF([Status]='Closed',1,0)")
-
Nic;o)
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
YOU MY FRIEND ARE INGENIUS. That worked great. I will need to do some homework on that one. Thank you so much.
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: The Dreaded DCOUNT Function in a Report
Have been "fighting" with these Access "features" for years <LOL>
Success with your application !
Nic;o)
| | Newbie | | Join Date: Oct 2007
Posts: 17
| | | re: The Dreaded DCOUNT Function in a Report
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.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,366 network members.
|
