crosstab quarters - jet does not recognise " as valid field name 1

[tchaplin]

Ive got a cross tab query that counts jobs per quarter for teh year.
It works fine for previous years because there is data for all quarters of that year. The query is bound to a report with the data source being 1 for quarter 1 - 2 for quarter 2 etc to 4.
However when there is no data in the quarter eg 3 for this year there is an error "jet does not recognize " as valid field name" because there is no data for this field.

Surely someone has come across a work around? Any suggestions Thanks

 

[PHV]

Use the IN (...) expression in the PIVOT clause of your crosstab query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tchaplin]

Thanks yet that achieved what I was after.

It was something I had never used before although know ill use in the future.

Thanks once again