Crosstab report problem

[Cordeiro82]

Hi,

I am creating a crosstab report based on a category picked in the category form.

A sample of the report design is shown below
-----------
DISTRIBUTION AMOUNT BY CATEGORY
Category: Metal
VendorName Miami Boston L.A San Diego Total
A 2 4 2 2 10
C 1 3 4 8
Total 3 7 2 6 18
---------------

The column totals were created as seperate text boxes added to the category footer so the Boston total is SUM([Boston])

The problem I am having is that for some categories there are no sales in a certain city (eg: there are no vendors that sell plastic in Boston) this causes the report to give me an error saying "The microsoft jet database engine does not recognize [Boston] as a valid field name or expression.

Any help would be appreciated.

 

[Cordeiro82]

Just wanted to add some more information to my question so as to give you a better idea of what is going on.

I am first creating a crosstab query based on the category picked. The problem is that the columns in the crosstab query are always dynamic as the number of cities that show up vary by the category picked (sometimes there are four columns(four cities) and sometimes there is only one. Is there any way to have access create a new report each time with the x number of columns and the column totals?

Any suggestion would be helpful. Thank you.