Nz - need code -

[Bibi]

I have a crosstab query that yields no value where I need a 0 value for
further calculation.

I do not write code:
This is the SQL for the crosstab.

TRANSFORM Sum(qLeaseEndDataAllDatesStatusJoin.Count) AS SumOfCount
SELECT qLeaseEndDataAllDatesStatusJoin.[Lease Month],
qLeaseEndDataAllDatesStatusJoin.[Lease Year],
Sum(qLeaseEndDataAllDatesStatusJoin.Count) AS [Total Of Count]
FROM qLeaseEndDataAllDatesStatusJoin
GROUP BY qLeaseEndDataAllDatesStatusJoin.[Lease Month],
qLeaseEndDataAllDatesStatusJoin.[Lease Year]
PIVOT qLeaseEndDataAllDatesStatusJoin.Status;

I'm pretty sure I need to add an Nz somewhere - just don't know
where......could someone please help?

 

[Duane Hookom]

Try:
TRANSFORM Val(Nz(Sum(qLeaseEndDataAllDatesStatusJoin.Count),0)) AS
SumOfCount
SELECT qLeaseEndDataAllDatesStatusJoin.[Lease Month],
qLeaseEndDataAllDatesStatusJoin.[Lease Year],
Sum(qLeaseEndDataAllDatesStatusJoin.Count) AS [Total Of Count]
FROM qLeaseEndDataAllDatesStatusJoin
GROUP BY qLeaseEndDataAllDatesStatusJoin.[Lease Month],
qLeaseEndDataAllDatesStatusJoin.[Lease Year]
PIVOT qLeaseEndDataAllDatesStatusJoin.Status;

 

[Bibi]

Thank you so much! I was really struggling with this.

TIA
Bibi

Try:
TRANSFORM Val(Nz(Sum(qLeaseEndDataAllDatesStatusJoin.Count),0)) AS
SumOfCount
SELECT qLeaseEndDataAllDatesStatusJoin.[Lease Month],
qLeaseEndDataAllDatesStatusJoin.[Lease Year],
Sum(qLeaseEndDataAllDatesStatusJoin.Count) AS [Total Of Count]
FROM qLeaseEndDataAllDatesStatusJoin
GROUP BY qLeaseEndDataAllDatesStatusJoin.[Lease Month],
qLeaseEndDataAllDatesStatusJoin.[Lease Year]
PIVOT qLeaseEndDataAllDatesStatusJoin.Status;

--
Duane Hookom
MS Access MVP

I have a crosstab query that yields no value where I need a 0 value for
further calculation.

I do not write code:
This is the SQL for the crosstab.

TRANSFORM Sum(qLeaseEndDataAllDatesStatusJoin.Count) AS SumOfCount
SELECT qLeaseEndDataAllDatesStatusJoin.[Lease Month],
qLeaseEndDataAllDatesStatusJoin.[Lease Year],
Sum(qLeaseEndDataAllDatesStatusJoin.Count) AS [Total Of Count]
FROM qLeaseEndDataAllDatesStatusJoin
GROUP BY qLeaseEndDataAllDatesStatusJoin.[Lease Month],
qLeaseEndDataAllDatesStatusJoin.[Lease Year]
PIVOT qLeaseEndDataAllDatesStatusJoin.Status;

I'm pretty sure I need to add an Nz somewhere - just don't know
where......could someone please help?