The Dreaded DCOUNT Function in a Report

[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;

[NeoPa]

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.

[JConsulting]

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

[bmyers]

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;

[NeoPa]

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.

[bmyers]

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]')")

[NeoPa]

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?

[bmyers]

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, ROAImagingQueWe b-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?

[bmyers]

Hi again folks,

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

Brian

[NeoPa]

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.