how to replace null value with zero in crosstab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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;
 
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;
Click to expand...
 
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;
Click to expand...
Click to expand...
 
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;
Click to expand...
Click to expand...
 
Back
Top