Prev: Sum Problem
Next: Dividinglines property in Access reports
From: Roger Denison on 2 Jul 2008 13:54
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?
From: KARL DEWEY on 2 Jul 2008 15:53
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


"Roger Denison" wrote:

> 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?
From: Roger Denison on 2 Jul 2008 17:15
That was it. Thanks a million. I think remember using that before. Thanks
for the refresher.

"KARL DEWEY" wrote:

> 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
>
>
> "Roger Denison" wrote:
>
> > 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?
 | 
Pages: 1
Prev: Sum Problem
Next: Dividinglines property in Access reports