Can you narrow down to a specific word in a Dlookup?

[smc678]

Im trying to Dcount in a report agianst a query but I need to narrow down to a specific name within the [Catgory] for example [Category] ='Allowable' How do I fit that into the statement?


=Nz(DCount("[Category]","qryAttendanceHistorybyDept","[Assoc Nm]=Reports!rptAssociateAttendanceHistorybyDept![Assoc Nm]"),0)

[rzw0wr]

SELECT Allowable, Nz(Dcount("[Allowable]","qryAttendanceHistoryByDept")) AS MyCount FROM YourTableHere WHERE Allowable = True

Untested and basically a guess.
Dale

[smc678]

Did not work. I need it to match the Assoc Nm on both report and query to return only those results

[ssanfu]

In looking at the syntax for the DCount function, in the criteria clause, the value (text) has to be concatenated to the field name. I'm not sure this will work because it looks like you are trying to get a value from a report. I've never used a value from a report, only forms...... sooooo .......

But try this:

=Nz(DCount("[Category]","qryAttendanceHistorybyDept", "[Assoc Nm] = '" & Reports!rptAssociateAttendanceHistorybyDept![Assoc Nm] & "'"),0)

Note the delimiters in the criteria clause....

[rzw0wr]

SELECT Allowable, Nz(Dcount("[Allowable]","qryAttendanceHistoryByDept")) AS MyCount FROM YourTableHere WHERE Allowable = True

Can you explain why/where this did not work.
I am trying to better understand SQL and hoping your explanation will help.

Thanks,
Dale