Nz - need code -

  • Thread starter Thread starter Bibi
  • Start date Start date
B

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?
 
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;
 
Thank you so much! I was really struggling with this.

TIA
Bibi


Duane Hookom said:
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

Bibi said:
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?
Click to expand...
Click to expand...
 
Back
Top