Criteria for use in DCount Function

[STRATMAN1]

I am in a query where I use a dsum function in several different expressions in the query. in the dsum, I am setting a criteria on a field in the same query table, i.e, where I sum dollar values from one field based on a range of values in another. Example: sum the dollar values from the for the blue team based on whether they fall into an xth percentile category. I can get the dsum statement to differentiate by percentile category.When I try to use a dcount function, statement constructed the same way, it will not recognize the criteria of the percentile categories. I am counting values from another field than the dollar value field, but everything else is the same. What happens is that the dcount statement returns a count of all the records, not just ones in the percentile category. Hope I explained well. Thanks for all good help.

 

[ChrisCarroll]

One workaround for the this kind of thing is to
DSum( Iif(<criteria>,1,0) )
perhaps? Not the greatest way out, I know.

OR: if you are counting, why not count the dollar field anyway, since you counting still works as 1 per record.

 

[PCLewis]

Using DSUM and DCOUNT with the same criteria and table should aggregate over the same records.

Verify this by doing the DCOUNT with the exact same parameters as the DSUM as per ChrisCarroll's suggestion.