Syntax DCount

[D Willett]

Hi
I have a grouped report ( each Client) which I need to count records in another query where the SiteCD is equal to SiteCD on the report.
The syntax I have is:

=DCount("JobID","qryData30-HandoverNull","SiteCD=SiteCD")

But this counts all Records from the query instead of each client. SiteCD can be ABC123 etc so I would presume this is a text field.

Can anyone help with the correct syntax?

Cheers

[HansV]

Hi Dave,

Use

=DCount("JobID","qryData30-HandoverNull","SiteCD=" & Chr(34) & [SiteCD] & Chr(34))

[D Willett]

Cheers Hans. Works a treat.

Regards

[D Willett]

Just a thought Hans, if there is a null value would I use nz in the expression as follows:

=DSum(Nz("ProgressDays",0),"qryData30-HandoverNull","SiteCD=" & Chr(34) & [SiteCD] & Chr(34))

Cheers

[HansV]

There shouldn't be a need for that, DSum will simply ignore Null values. (And the syntax isn't valid either)

However, if ProgressDays is Null in ALL records, DSum would return Null. If you'd rather return 0 instead, you can wrap DSum in Nz:

=Nz(DSum("ProgressDays","qryData30-HandoverNull","SiteCD=" & Chr(34) & [SiteCD] & Chr(34)),0)

[D Willett]

Yes I thought of wrapping it that way but I applied too many brackets.

The reason for the update is there may be times where there is no SiteCD on one side therefore null values will apply.
However, this has fixed that.

Again, Thanks.