how to replace null value with zero in crosstab query

[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;

 

[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;

 

[Guest]

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.

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;

 

[Guest]

I am using 2002 SP3 and it gives the zeros.

 

[Guest]

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

I am using 2002 SP3 and it gives the zeros.

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;

 

[OfficeDev18 via AccessMonster.com]

Are you sure that Tier ID is numeric?

Sam

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

I am using 2002 SP3 and it gives the zeros.

[quoted text clipped - 13 lines]

 

[Guest]

Yep, it set as a long integer. Anyway to do it using IIF?

Alan

Are you sure that Tier ID is numeric?

Sam

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

I am using 2002 SP3 and it gives the zeros.

[quoted text clipped - 13 lines]

GROUP BY Inventory.Type
PIVOT Inventory.Status;