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
Syntax DCount
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Syntax DCount
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Syntax DCount
Hi Dave,
Use
=DCount("JobID","qryData30-HandoverNull","SiteCD=" & Chr(34) & [SiteCD] & Chr(34))
Use
=DCount("JobID","qryData30-HandoverNull","SiteCD=" & Chr(34) & [SiteCD] & Chr(34))
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Syntax DCount
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
=DSum(Nz("ProgressDays",0),"qryData30-HandoverNull","SiteCD=" & Chr(34) & [SiteCD] & Chr(34))
Cheers
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Syntax DCount
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)
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)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Syntax DCount
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.
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.
Cheers ...
Dave.
Dave.