Crosstab query based Reports

[Addy]

Hello all,

I am having on what might be a minor problem.

I have based my report on a cross tab query. There is a field called
City on the original non cross tab query which has 5 cities that always
show up.

I have made a cross tab query from the Original query to report based
on the 5 cities. So the report looks something like this"

Sales Margin City1 City2 City3 City4 City5
---------------------------------------------------------------------------------------

The data is downloaded montly from a server, with the old data being
deleted. The problem is that not always will there be data for all the
cities. This month there was no data for City2 so I got a JET SQL error
saying that City2 is not a valid field name or expression when I ran
the report.

Is there a way to supress this error if no data comes up?

Thanks.

 

[John Spencer]

Best way is to force the query to always return the five cities. You said
they were static. So, in the crosstab query, click on properties, in Column
heading property for the query, enter "City1","City2", "City3", "City4",
"City5"

Once you have done that the query will always return those five columns (no
more, no less). If there is no data for one of the columns, the column will
contain all nulls, but it will be there.