|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 9th, 2010, 01:33 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Nz - need code -
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? -- TIA Bibi 
|
|
#2
March 9th, 2010, 05:12 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Nz - need code -
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" wrote in message ... 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? -- TIA Bibi
|
|
#3
March 9th, 2010, 12:21 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Nz - need code -
Thank you so much! I was really struggling with this.
TIA Bibi "Duane Hookom" wrote: 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" wrote in message ... 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? -- TIA Bibi
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
