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.
[code=vb]
me.mytextbox = nz(DCount("[Status]","ROA_Imaging_ QueWeb_LINKED_Q uery","[Status] Like 'Closed' "),0)
[/code]
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.

[bmyers]

The saga continues...I'v e 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.

[NeoPa]

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.

[bmyers]

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

[bmyers]

The new post is up. Thanks for the heads up.

[missinglinq]

Just to make things simple I've mergerd the two posts!

Welcome to The Scripts, Brian!

Linq ;0)>