Getting a count query to return zero.

  • Thread starter Thread starter JohnB
  • Start date Start date
J

JohnB

Hi. When I create a query set to count on a tables
autonumber ID field and with a criteria as another fields
content, it doesn’t return a zero value if no records are
found. It appears to return nothing at all. Is there any
way I can get a query like this to return zero in cases
like this? The reason I ask is that Im setting up a report
to show various totals in my db. Each total is produced by
a separate query and then each query is used in the
reports record source SQL. None of these separate queries
are linked in anyway in the record source SQL. All works
OK until I introduce a query that doesnt produce a total
figure. Then it stops all the other queries totals
appearing. If I could get it to return zero, presumably it
would work.

Any ideas on this one? Thanks, JohnB.
 
Rather than use a query, use the DCount function:
If DCount("[NameOfIDField]","NameOfTable","[NameOfOtherField] =
'YourCriteria'") = 0 Then
 
Thanks. Ive not used DCount before. Ill have a look at it.
Looks like it may be a lot easier than the method I was
trying. Cheers, JohnB
-----Original Message-----
Rather than use a query, use the DCount function:
If DCount
Click to expand...
("[NameOfIDField]","NameOfTable","[NameOfOtherField] =
 
I'm not familiar with DCount, but with DSum and DSum will only retun Null
instead of zero if there is nothing to sum. The work around is to put a NZ()
around the Dsum. Putting the NZ() aroung your Dcount might work to, and
return a zero.

Hope that helps.

JohnB said:
Thanks. Ive not used DCount before. Ill have a look at it.
Looks like it may be a lot easier than the method I was
trying. Cheers, JohnB
-----Original Message-----
Rather than use a query, use the DCount function:
If DCount
Click to expand...
("[NameOfIDField]","NameOfTable","[NameOfOtherField] =
'YourCriteria'") = 0 Then

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com





.
Click to expand...
Click to expand...
 
try NZ() around Dcount. It works with Dsum, which returns null instead of
zero without the NZ() ex NZ(Dcount......) should return 0 if no records.

JohnB said:
Thanks. Ive not used DCount before. Ill have a look at it.
Looks like it may be a lot easier than the method I was
trying. Cheers, JohnB
-----Original Message-----
Rather than use a query, use the DCount function:
If DCount
Click to expand...
("[NameOfIDField]","NameOfTable","[NameOfOtherField] =
'YourCriteria'") = 0 Then

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com





.
Click to expand...
Click to expand...
 
Back
Top