count if no record, then return zero value

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

i have a query to count record,

however, if there is no record

then there is no value

how can i make it to "ZERO" value if no record counted.

Thx a lot.

Tony
 
Tony,

It depends a bit where you want to use the record count, which I presume
is ultimately on a form or report. The DCount() function returns a 0 if
there are no records.
 
Hi Steve

thanks for a quick advice.

however the count result (1st query) is ultimately used in another qeury
(2nd query) which come up one ROW record to merge the result into report.

2nd query combines all the one ROW qeries (similar to 1st query), all
queries have no relation.

Because this 1st query return no record, then 2nd query return no record,
then the report show nothing.

any other way?? thanks.
 
Tony,

In that case, DCount() should work well.

It is difficult for me to be specific without any detail of your
data/queries. But another option to explore is that probably you need a
Left Join in your 2nd Query to link your other table(s) with the 1st
Query, and then use the Nz() function on the count field to return 0 if
there is no record in 1st Query.
 
Hi Steve

Thanks for your assistance.

How come i cannot run the script when i change the 1st line from Count to
Dcount.

SELECT Count(case.Running) AS [number], letters.letter
FROM [case] INNER JOIN letters ON case.ID = letters.ID
WHERE (((letters.deadline)>Now()) AND ((case.ID) Not In (Select letters.id
from letters where letters.letter = "36A6")))
GROUP BY letters.letter
HAVING (((letters.letter)="36A5"));
 
I also am in need of a query\report that will return zeros when ther
are no records. I'm combining two tables into one query. The firs
contains a list of sales campaigns. The second a list of leads. I'
building a query that tells me how many leads have been generated b
each campaign. Currently am using the Count function. Works fine o
those campaigns that have leads, but I also need to know whic
campaigns don't have leads. I'm willing to try both the DCount functio
and the NZ function, but I'm enough of a novice that I don't know wha
to put in the (). Can you help
 
Tony,

DCount is a Domain Aggregate Function. It is not used in the same way
as Count().
 
Back
Top