convert blank record to zero

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

Tony

i have a query to sum up records by month into different categories.

But there is no records in some categories. Then the result would be blank
in some fields.

how can i make those blank records into zero? in query or report module?

Like this

A
B 10

i want this

A 0
B 10

Thanks a lot.
 
Hi

Use the Nz() function
...., SUM(Nz(YourField,0)) As YourSum, ...

Arvi Laanemets
 
Thx Arvi

Sorry, in fact, it is count

I tried Count(Nz((letters.letter)) in query but still failed to see "0"

i know Nz command in report module. But the count process is done in query
module.

Could you give me more help? thanks a lot.

******************************
TRANSFORM Count(letters.letter) AS [Number of letters issued]
SELECT Month([issue-date]) AS [Month]
FROM letters
GROUP BY Month([issue-date])
ORDER BY Month([issue-date])
PIVOT letters.letter;
*****************************
 
Hi

Sorry, but I have never needed a crosstable query so long. I'm sure somebody
with more experience in this matter can help you. But I can't see how you do
want to count non-existing letters? Maybe you have to count on some other
field?


Arvi Laanemets


Tony said:
Thx Arvi

Sorry, in fact, it is count

I tried Count(Nz((letters.letter)) in query but still failed to see "0"

i know Nz command in report module. But the count process is done in query
module.

Could you give me more help? thanks a lot.

******************************
TRANSFORM Count(letters.letter) AS [Number of letters issued]
SELECT Month([issue-date]) AS [Month]
FROM letters
GROUP BY Month([issue-date])
ORDER BY Month([issue-date])
PIVOT letters.letter;
*****************************

Arvi Laanemets said:
Hi

Use the Nz() function
..., SUM(Nz(YourField,0)) As YourSum, ...

Arvi Laanemets
Click to expand...
Click to expand...
 
Back
Top