Dcount with Different criteria

[jonnj]

I have a report that simply has first name, last name and the division they are in, Either Div 1, 2 or 3,

I want to total the number of people in each Division. What started off as a simple task I thought, has become a headache.

I started with(for example):
=Dcount("[Div]","Gen Info","[Div]="1"")where Gen Info was the table. This totaled all the 1's in the table not the report. I replaced the table with the queryname in the dcount function. . This came up #error.


I tried =abs(sum([div]="1")), but this only works in the detail, and having to total all 3 div, would increase the line spacing.

Any direction or solution would be greatly appreciated

Thank you
John

 

[kendue]

Couldn't you put your control for the summation in the detail, then sum the control in a group footer grouped on "div", and set the detail as not visible or "cancel print"? That way only your group totals would print.

 

[dhookom]

Assuming Div is a text field, this expression should work in report or group headers or footers:

 =abs(sum([div]="1"))

If Div is numeric, try:

 =abs(sum([div]=1))

This will not work in the page sections.

Duane
Hook'D on Access
MS Access MVP

 

[jonnj]

Thx dhookom that solved my problem.