DCount with Multiple Criteria

Good Afternoon,

I need some help with an Access report. I am using Access 2003 with a database in Access 2000 file format. I am also pretty new to coding and have no idea what I’m doing there, so I’m trying to keep it simple if possible.

I am trying to create a report that counts the number of cases by multiple criteria. It is a year end report, so there will be 60 separate counts. I have one table that I am pulling data from that contains several fields including CaseID, Division, FY, ActionCode and Status Code. I want to count the number of cases for a specific year (FY), division (one of two locations), action (one of ten different actions) and status (complete or pending).

I began by creating several queries in order to be able to use Dcount with only one criteria. However, when I got up to 12 separate queries and was only halfway complete, I decided there has to be another way to pull the info out of the one table instead.

This is the function I have in the control source of my unbound text box in the report so far:

=DCount (“[CaseID]”,“tbl2005CaseLog”,“[ActionCode] = ‘PADM’ AND [Division] = ‘G’”)

It gives me an error saying Invalid Syntax, you many have entered an operand without an operator.

If I remove the second criteria and leave it as this, it works just fine:

=DCount("[CaseID]","tbl2005CaseLog","[ActionCode] = 'PADM'")

Can anyone explain to me how to add more than one criteria to a Dcount? I would ideally like to use 3-4 criteria.

Thanks so much for the help.

Jaimee

That looks okay, unless that space is really after DCount. Have you tried a totals query that groups on the various fields? It would be a lot easier and more dynamic. It would also run faster than a report with a lot of DCounts in it.

Thank you for your reply

No, the space is not actually after Dcount, that was my typo.

I also looked at the Totals query and could not figure out how to count on more than one criteria. For example If I put in Case ID for the field, which is what I want counted, where do I put [ActionCode]=PADM and [FY]=05 and [Status]=Pend and [Division]=G, because those are separate fields than CaseID and I need to set a value for each of my 4 criteria. And wouldn't this also require me to make a separate query for each value I need counted (60 of them) because I would need a second query that pulled records where [ActionCode]=PADM and [FY]=05 and [Status]=Pend and [Division]=LC and so on 59 more times?

I'm not concerned about how long it takes to run the report. It won't be used often, but it has to be readily accessible in a printable format, which is why I chose a report.

I may be completely missing this but I'm still really confused!!

Can you post a sample db? As to the query, I envision one which automatically returns all the counts for the various combinations. It may not actually be feasible, depending on your structure and needs, it was just a thought. The sample db may also help with that.

Hi JLee3782
I have been pondering the very same thing and was reading your post when I had a "lightbulb moment"

I am wanting to count all boolean values that are true within a group called REGION. I want the result to be displayed in the footer area at the end of each group.

So in the footer area, I put in a text box and set the control source = group.
I called this textbox "X" (Just to test it)

Then, to count the relevant data I have written the following.
=DCount([EssCheckFormRtnd],"[QryQRtnFormsReturnedByHCs]","EssCheckFormRtnd=True AND [region] =[x].value")

[EssCheckFormRtnd] is a boolean value which identifies if the form has been returned.

[QryQRtnFormsReturnedByHCs] is my query
EssCheckFormRtnd=True - If BOTH this value is TRUE,
AND [region] =[x].value") - and the region = value in my text box, then count it.

Not sure that this is neccessarily the correct / best way to do, but it works for me!
Hope this will assist you