Display 0 Count on Query

[Guest]

I am trying to run a query where it is important to get the result of 0,
instead of Null. Here is a simple example of how my query looks now. Table
is "Master Sales Data" and the field is "Sale DIGI":

SELECT Count([Master Sales Data].[Sale DIGI]) AS [CountOfSale DIGI]
FROM [Master Sales Data];

NOTE: My queries are a bit more complicated and numerous than this one, but
my hope is that if someone can make the change to this formula, I can
comprehend the concept and implement it across the board. (Thanks!)

 

[Allen Browne]

Use Nz() to convert the null to a zero:
SELECT Nz(Count([Master Sales Data].[Sale DIGI]),0) AS [CountOfSale DIGI]
FROM [Master Sales Data];

Access often misunderstands this type of data, so it would be worth using
CLng() as well:
SELECT CLng(Nz(Count([Master Sales Data].[Sale DIGI]),0)) AS [CountOfSale
DIGI] FROM [Master Sales Data];

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Fantastic! I added some criteria as follows, seems perfect!

SELECT Nz(Count([Master Sales Data].[Sale DIGI]),0) AS [CountOfSale DIGI]
FROM [Master Sales Data]
WHERE ((([Master Sales Data].[Sale DIGI])=Yes) AND (([Master Sales
Data].[Sale Date])=Date()));

I tried using CLng() as well, but had bracket issues. If you have time, I
would love to try that, but it is no big deal.

THANK YOU VERY MUCH!
--
Mark Matzke

Use Nz() to convert the null to a zero:
SELECT Nz(Count([Master Sales Data].[Sale DIGI]),0) AS [CountOfSale DIGI]
FROM [Master Sales Data];

Access often misunderstands this type of data, so it would be worth using
CLng() as well:
SELECT CLng(Nz(Count([Master Sales Data].[Sale DIGI]),0)) AS [CountOfSale
DIGI] FROM [Master Sales Data];

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I am trying to run a query where it is important to get the result of 0,
instead of Null. Here is a simple example of how my query looks now.
Table
is "Master Sales Data" and the field is "Sale DIGI":

SELECT Count([Master Sales Data].[Sale DIGI]) AS [CountOfSale DIGI]
FROM [Master Sales Data];

NOTE: My queries are a bit more complicated and numerous than this one,
but
my hope is that if someone can make the change to this formula, I can
comprehend the concept and implement it across the board. (Thanks!)

 

[Allen Browne]

Good: you have it working.

The CLng() should go around the entire expression, i.e.:
SELECT CLng(Nz(Count([Master Sales Data].[Sale DIGI]),0)) AS [CountOfSale
DIGI]
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Fantastic! I added some criteria as follows, seems perfect!

SELECT Nz(Count([Master Sales Data].[Sale DIGI]),0) AS [CountOfSale DIGI]
FROM [Master Sales Data]
WHERE ((([Master Sales Data].[Sale DIGI])=Yes) AND (([Master Sales
Data].[Sale Date])=Date()));

I tried using CLng() as well, but had bracket issues. If you have time, I
would love to try that, but it is no big deal.

THANK YOU VERY MUCH!
--
Mark Matzke

Use Nz() to convert the null to a zero:
SELECT Nz(Count([Master Sales Data].[Sale DIGI]),0) AS [CountOfSale DIGI]
FROM [Master Sales Data];

Access often misunderstands this type of data, so it would be worth using
CLng() as well:
SELECT CLng(Nz(Count([Master Sales Data].[Sale DIGI]),0)) AS [CountOfSale
DIGI] FROM [Master Sales Data];

in
message

I am trying to run a query where it is important to get the result of 0,
instead of Null. Here is a simple example of how my query looks now.
Table
is "Master Sales Data" and the field is "Sale DIGI":

SELECT Count([Master Sales Data].[Sale DIGI]) AS [CountOfSale DIGI]
FROM [Master Sales Data];

NOTE: My queries are a bit more complicated and numerous than this
one,
but
my hope is that if someone can make the change to this formula, I can
comprehend the concept and implement it across the board. (Thanks!)