Cross tab qry for report

[Fotorat]

Access 97 windows XP Pro

I have done a crosstab query for a report with 12 months as the columns and job codes as rows counting people as the value:

Link to view report so far

Ps the zeros in there so far came from an outer join so I could show Job codes with no transfers or leavers and I used rotate text active X for the headers.

I have 2 questions

1- How can I force "0" in the null values of the pivot so that my row totals in the report work? This is the crosstab (I cant seem to get IIF - IsNull to work!!):

TRANSFORM Sum(IIf([CODE]="L",[TOTAL],[TOTAL])) AS SumOfTOTAL
SELECT UNION.JOB
FROM [UNION]
WHERE (((UNION.JOB) Not Like "**Other - Non RetailPosition* *" And (UNION.JOB) Not Like "Dec*"))
GROUP BY UNION.JOB
ORDER BY [UNION].[MONTH] & '-' & [CODE]
PIVOT [UNION].[MONTH] & '-' & [CODE];


2- How can I assign 0,1,2,3,4,5,6,7 ,8,9,10,11,12 to my cross tab columns so that the report detail txt field names dont change each month when I increment the report 1 month.

if suggesting code please provide examples!

[NeoPa]

In answer to question one - if the 'IIf(IsNull([Field]),0,[Field])' construct doesn't work try Nz([Field],0)

[Fotorat]

Brilliant - many thanks

I had tried that earlier with no success but this worked:

TRANSFORM Nz(Sum(IIf([CODE]="L",[TOTAL],[TOTAL])),0) AS SumOfTOTAL

No if anyone can start me of one assigning 0 to 12 for the last 12 months

I am clutching at straws like this:

MonthsAgo: IIf(DatePart("m ",[EFFDT])=DateAdd("m",-1,Now()),"-1","next iif")

[MMcCarthy]

MonthsAgo: IIf(DatePart("m ",[EFFDT])=DateAdd("m",-1,Now()),"-1","next iif")

I think you're looking for

MonthsAgo: Month(Now()) - Month([EFFDT])

[PEB]

To assign the months replace the last line with this one:

PIVOT ([UNION].[MONTH] & '-' & [CODE]) in (1, 2, 3, 4,5,6,7,8,9,10, 11,12);