Creating and Recoding Variables in Crosstab Query

[lemo]

i am working on a Crosstab query. everything works fine until i start creating or recoding variables. then i receive 2 types of errors -

- 'Data type mismatch in criteria expression' and
- 'Access database engine does not recognize [borough] as valid field name or expression'.

now, i think i am on my way to resolving the first issue - seems like Crosstab queries are sensitive to different data types in the same expression. this - OC: IIf([Overall Condition]="A",1,IIf([Overall Condition]="U",0,"NA")) - doesn't work because "NA" is text and 1 and 0 are numeric. (please confirm).

second one - i have no idea. it looks like Crosstab query doesn't like when i create a variable and then use that new variable to create yet another variable? is it possible?

thanks.
l

the code is -

TRANSFORM First(FeatureRatings.Rating) AS FirstOfRating
SELECT ALLSITES.Boro, IIf([boro]="b","Brooklyn",IIf([boro]="m","Manhattan",[boro])) AS Borough, ALLSITES.District, [borough] & [district] AS BoDi, ALLSITES.[Prop ID], InspectionMain.Date, IIf([Overall Condition]="A","1",IIf([Overall Condition]="U","0","NA")) AS OC, InspectionMain.Cleanliness, InspectionMain.[Inspection ID]
FROM ALLSITES INNER JOIN (InspectionMain INNER JOIN FeatureRatings ON InspectionMain.[Inspection ID] = FeatureRatings.[Inspection ID]) ON ALLSITES.[Prop ID] = InspectionMain.[Prop ID]
WHERE (((InspectionMain.Date) Between #7/1/2007# And Date()-14))
GROUP BY ALLSITES.Boro, IIf([boro]="b","Brooklyn",IIf([boro]="m","Manhattan",[boro])), ALLSITES.District, [borough] & [district], ALLSITES.[Prop ID], InspectionMain.Date, IIf([Overall Condition]="A",1,IIf([Overall Condition]="U",0,"NA")), InspectionMain.Cleanliness, InspectionMain.[Inspection ID]
ORDER BY ALLSITES.Boro, ALLSITES.District, ALLSITES.[Prop ID], InspectionMain.Date
PIVOT FeatureRatings.Feature;

 

[lemo]

thanks Pat.
l