Nz - need code -
in [Queries]
|
Prev: Brain Freeze help with combobox and query
Next: recursively retrieve records from 2nd table based on records from
From: Bibi on 8 Mar 2010 20:33 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
From: Duane Hookom on 9 Mar 2010 00:12 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" <Bibi(a)discussions.microsoft.com> wrote in message news:B6FAFF17-0493-4A1E-9E93-A1C55051717B(a)microsoft.com... > 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
From: Bibi on 9 Mar 2010 07:21
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" <Bibi(a)discussions.microsoft.com> wrote in message > news:B6FAFF17-0493-4A1E-9E93-A1C55051717B(a)microsoft.com... > > 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 > |