DCount on a Report

[darude]

Hi All,
Can anyone look at the following DCount and see if there is anything wrong with the syntax.

=DCount("[mastername]","qry_employergrouped_unique","[programlevel]= ' " & [programlevel] & "' And [emptype]='private' And [splacement]='1'&quot

Thank you in advance.

 

[dhookom]

It is hard to tell when we don't know the data types. You have an extra space after "[programlevel]= ' " and [splacement] is assumed to be string.

If qry_EmployerGrouped_Unique is the record source of the report then DCount() is not necessary. You could replace this expression with something much more efficient.


Duane
MS Access MVP

 

[darude]

If you have any suggestions dhookom, what could be more efficient. This my first time utilizing DCount. Thank you.

 

[dhookom]

If qry_EmployerGrouped_Unique is the record source of the report then you can use a control source in the ProgramLevel Header or Footer section like:
=Abs(Sum([emptype]="private" And [splacement]="1&quot)
This would be much more efficient since it uses the report's record source rather than creating a new record set which is done by DCount(). Plus, if your report is opened with a where clause, it may filter out certain records and the DCount() expression would refer to all records.



Duane
MS Access MVP

 

[darude]

That's a great idea except that the query my report is based on is not unique. I'm trying to get a unique count of employers by going out to another query. Thank you for the tip anyway.

 

[dhookom]

That's why I stated in my original reply that they had to be the same. If you are grouping by Employer, you can simply add a text box to the group header:
Name: txtCountEmployer
Control Source: =1
Running Sum: Over All (or group)

Then in your report footer, add a text box with a control source of:
=txtCountEmployer

Again, this will only work if you are grouping by Employer.

Duane
MS Access MVP