how to replace null value with zero in crosstab query

R

Rugby15ace

I have a crosstab query which basically count the different types of
inventory we have and their status. Sometimes the value will be null because
the inventory doesn't have any in a particular status. I want to be able to
place zeros where the null values occur. I've tried the Nz function, but it
gives me the error cannot have aggregate function in WHERE clause
(Count(Inventory.[TierID]). I am using 2007 Beta. Am I typing something
incorrectly? Any help would be appreciated. Thanks.

Alan

TRANSFORM Nz(Count(Inventory.[Tier ID]),0) AS [CountOfTier ID]
SELECT Inventory.Type, Count(Inventory.[Tier ID]) AS [Total Of Tier ID]
FROM Inventory
GROUP BY Inventory.Type
PIVOT Inventory.Status;
 
O

OfficeDev18 via AccessMonster.com

Nz(Count(Inventory.[Tier ID]),0) should be Count(Nz(Inventory.[Tier ID],0))

Also, I think it should appear that way in the SELECT clause as well.

Hope this helps,

Sam
I have a crosstab query which basically count the different types of
inventory we have and their status. Sometimes the value will be null because
the inventory doesn't have any in a particular status. I want to be able to
place zeros where the null values occur. I've tried the Nz function, but it
gives me the error cannot have aggregate function in WHERE clause
(Count(Inventory.[TierID]). I am using 2007 Beta. Am I typing something
incorrectly? Any help would be appreciated. Thanks.

Alan

TRANSFORM Nz(Count(Inventory.[Tier ID]),0) AS [CountOfTier ID]
SELECT Inventory.Type, Count(Inventory.[Tier ID]) AS [Total Of Tier ID]
FROM Inventory
GROUP BY Inventory.Type
PIVOT Inventory.Status;
 
R

Rugby15ace

Hey Sam, I tried what you suggested. It didn't give me any errors, but it
didn't change the null values to zero. Any other ideas? Thanks.

OfficeDev18 via AccessMonster.com said:
Nz(Count(Inventory.[Tier ID]),0) should be Count(Nz(Inventory.[Tier ID],0))

Also, I think it should appear that way in the SELECT clause as well.

Hope this helps,

Sam
I have a crosstab query which basically count the different types of
inventory we have and their status. Sometimes the value will be null because
the inventory doesn't have any in a particular status. I want to be able to
place zeros where the null values occur. I've tried the Nz function, but it
gives me the error cannot have aggregate function in WHERE clause
(Count(Inventory.[TierID]). I am using 2007 Beta. Am I typing something
incorrectly? Any help would be appreciated. Thanks.

Alan

TRANSFORM Nz(Count(Inventory.[Tier ID]),0) AS [CountOfTier ID]
SELECT Inventory.Type, Count(Inventory.[Tier ID]) AS [Total Of Tier ID]
FROM Inventory
GROUP BY Inventory.Type
PIVOT Inventory.Status;
 
R

Rugby15ace

Thanks Karl, so it's probably something with the Beta version then.

KARL DEWEY said:
I am using 2002 SP3 and it gives the zeros.

Rugby15ace said:
I have a crosstab query which basically count the different types of
inventory we have and their status. Sometimes the value will be null because
the inventory doesn't have any in a particular status. I want to be able to
place zeros where the null values occur. I've tried the Nz function, but it
gives me the error cannot have aggregate function in WHERE clause
(Count(Inventory.[TierID]). I am using 2007 Beta. Am I typing something
incorrectly? Any help would be appreciated. Thanks.

Alan

TRANSFORM Nz(Count(Inventory.[Tier ID]),0) AS [CountOfTier ID]
SELECT Inventory.Type, Count(Inventory.[Tier ID]) AS [Total Of Tier ID]
FROM Inventory
GROUP BY Inventory.Type
PIVOT Inventory.Status;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top