Dcount Error

[waqas]

Dear Experts.

i have two tables in master tables all employees and in detail table worked employees data by date. i am counting shift wise quantity from detali table but i want to count how many employees are existing in particluar group from master i wrote dcount function which is giving error

kindly chech where i am wrong.

SELECT UpDateShiftReport.Terminal,
DCount("[Empno]","RostimaList","[RoleGroup] = " & [Group]) AS HC, UpDateShiftReport.Group,
Sum(IIf([TandARoster]="07:00-18:00" And [MNM]="M" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [0700-1800-M],
Sum(IIf([TandARoster]="07:00-22:00" And [MNM]="M" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [0700-2200-M],
Sum(IIf([TandARoster]="11:00-22:00" And [MNM]="M" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [1100-2200-M],
Sum(IIf([TandARoster]="18:00-07:00" And [MNM]="M" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [1800-0700-M],
Sum(IIf([TandARoster]="22:00-07:00" And [MNM]="M" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [2200-0700-M],


Sum(IIf([TandARoster]="07:00-18:00" And [MNM]="NM" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [0700-1800-NM],
Sum(IIf([TandARoster]="07:00-22:00" And [MNM]="NM" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [0700-2200-NM],
Sum(IIf([TandARoster]="11:00-22:00" And [MNM]="NM" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [1100-2200-NM],
Sum(IIf([TandARoster]="18:00-07:00" And [MNM]="NM" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [1800-0700-NM],
Sum(IIf([TandARoster]="22:00-07:00" And [MNM]="NM" And [eqpassign]<>'Pooled' And [category]<>'Trng',1,0)) AS [2200-0700-NM],
Sum(IIf([AMPM]='AM' And [MNM]="NM" And [eqpassign]<>'Pooled' And [category]='Trng',1,0)) AS TrainingNM,
Sum(IIf([AMPM]='AM' And [MNM]="M" And [eqpassign]<>'Pooled' And [category]='Trng',1,0)) AS TrainingM,
Sum(IIf([AMPM]='AM' And [MNM]="M" And [eqpassign]='Pooled' And [category]<>'Trng',1,0)) AS PooledMAM,
Sum(IIf([AMPM]='AM' And [MNM]="NM" And [eqpassign]='Pooled' And [category]<>'Trng',1,0)) AS PooledNMAM,
Sum(IIf([AMPM]='PM' And [MNM]="M" And [eqpassign]='Pooled' And [category]<>'Trng',1,0)) AS PooledMPM,
Sum(IIf([AMPM]='PM' And [MNM]="NM" And [eqpassign]='Pooled' And [category]<>'Trng',1,0)) AS PooledNMPM
FROM RostimaList LEFT JOIN UpDateShiftReport ON RostimaList.EmpNo = UpDateShiftReport.EmployeeNumber
GROUP BY UpDateShiftReport.Terminal, UpDateShiftReport.Group, UpDateShiftReport.RosterDate
HAVING (((UpDateShiftReport.Group)<>'-') AND ((UpDateShiftReport.RosterDate) Between #7/24/2012# And #7/24/2012#))
ORDER BY UpDateShiftReport.Terminal, UpDateShiftReport.RosterDate;

[pbaldy]

I cant look at the sample right now. What's the data type of Group? If text:

http://access.mvps.org/access/general/gen0018.htm

Since the table is in the query, why not just list the field?

[waqas]

Dear Experts,

i have attached example db in this db i have add two tables. rostimalist is a master table and other updateshiftreport is a detail table.

in master table all records means all employees are existing in detail table in one day or in one date some employees are worked.

now i want to count from master tables all employees in each group and from detail how many worked in one day from each group.

i got count from detail table but i am facing problem counting from master table for this i have put dcount formula in my attached db query but it is giving me error both fields has same datatype as text length 32.

kindly chech my attached file and send me any solution.

thanks for your cooperation.

[pbaldy]

So you didn't bother to read the link I posted?

HC: DCount("[Empno]","RostimaList","[RoleGroup] = '" & [Group] & "'")