Report Error for certain Crosstab Query

[Roger Denison]

I'm developing a report that uses as its recordsource a crosstab query. All
works well until I run a query where one of the columns has no data. For
example, let's say I had a table that showed inventory at different stores
and a crosstab query that pivoted on ClothingType and I had "Mens" and
"Womens" in the records under ClothingType. If there are no "Mens" clothes I
get an error in the report. I can still run the crosstab and see that the
only column that shows up is "Womens". (ok, so all the stores are Victoria's
Secret %^P) But the report barfs with the following error:

The Microsoft Jet database engine does not recognize '' as a valid field
name or expression.

How do I address this?

 

[KARL DEWEY]

Open you crosstab query in design view and edit the PIVOT line like this ---

PIVOT Format([Date open],"mmm") In
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

adding the IN(.........) function listing all the possible column
outputs, in the order you want them. Then the column will always be in the
output.

 

[Roger Denison]

That was it. Thanks a million. I think remember using that before. Thanks
for the refresher.

Open you crosstab query in design view and edit the PIVOT line like this ---

PIVOT Format([Date open],"mmm") In
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

adding the IN(.........) function listing all the possible column
outputs, in the order you want them. Then the column will always be in the
output.
--
KARL DEWEY
Build a little - Test a little

I'm developing a report that uses as its recordsource a crosstab query. All
works well until I run a query where one of the columns has no data. For
example, let's say I had a table that showed inventory at different stores
and a crosstab query that pivoted on ClothingType and I had "Mens" and
"Womens" in the records under ClothingType. If there are no "Mens" clothes I
get an error in the report. I can still run the crosstab and see that the
only column that shows up is "Womens". (ok, so all the stores are Victoria's
Secret %^P) But the report barfs with the following error:

The Microsoft Jet database engine does not recognize '' as a valid field
name or expression.

How do I address this?