Crosstab ColumnHeaders

[Sean Howard]

This is a very fustration problem.

I have a crosstab query in MS Access that uses a data field (say called
"Brand") as the column headings. As I need several crosstab queries to
output data in a similar structure (basically with the same columns),
the best way SEEMED to use the ColumnHeadings property on every query to
ensure consistancy.
This all worked fine until is transpired that some of the "Brands" had
names that could not be directly used as FieldNames (e.g. "Cat.Hat
Motors").
Therefore I changed the process/code that created the ColumnHeadings
list to figure out what the column name WOULD be in the case of an
invalid field name. This Validation code simply replaces any invalid
fieldname characters with underscores.

In principle this all makes perfect sense (to me at least) but not to MS
Access.

1) If my crosstab has a column heading entry "Cat.Hat Motors" NO DATA
appears on that field
2) If my crosstab has a column heading entry "Cat_Hat Motors" NO DATA
from the "Cat.Hat" brand appears in this revised column.
3) If I remove ALL the ColumnHeadings, I CAN see data under the field
"Cat_Hat Motors"

It's almost as if I can only specify ColumnHeadings for "original" data,
and not the stuff that MS Access will create automatically to comply
with naming conventions.

Any ideas how to make ColumnHeadings work properly?
Help!!!!!!

Sean HOward

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tom van Stiphout]

On 27 Jun 2004 22:50:15 GMT, Sean Howard <se*********@axelero.hu>
wrote:

Try [square brackets] around the field names.
-Tom.

This is a very fustration problem.

I have a crosstab query in MS Access that uses a data field (say called
"Brand") as the column headings. As I need several crosstab queries to
output data in a similar structure (basically with the same columns),
the best way SEEMED to use the ColumnHeadings property on every query to
ensure consistancy.
This all worked fine until is transpired that some of the "Brands" had
names that could not be directly used as FieldNames (e.g. "Cat.Hat
Motors").
Therefore I changed the process/code that created the ColumnHeadings
list to figure out what the column name WOULD be in the case of an
invalid field name. This Validation code simply replaces any invalid
fieldname characters with underscores.

In principle this all makes perfect sense (to me at least) but not to MS
Access.

1) If my crosstab has a column heading entry "Cat.Hat Motors" NO DATA
appears on that field
2) If my crosstab has a column heading entry "Cat_Hat Motors" NO DATA
from the "Cat.Hat" brand appears in this revised column.
3) If I remove ALL the ColumnHeadings, I CAN see data under the field
"Cat_Hat Motors"

It's almost as if I can only specify ColumnHeadings for "original" data,
and not the stuff that MS Access will create automatically to comply
with naming conventions.

Any ideas how to make ColumnHeadings work properly?
Help!!!!!!

Sean HOward

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Sean Howard]

Try square brackets where exactly? Adding them to the ColumnHeadings
values will only add another underscore to every "Brand".
Sean

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tom van Stiphout]

On 28 Jun 2004 02:00:18 GMT, Sean Howard <se*********@axelero.hu>
wrote:

Agreed, I spoke too soon. I seem to remember I had luck with this
before, but indeed it doesn't work. Perhaps it's best to stick with
legal column names.

-Tom.

Try square brackets where exactly? Adding them to the ColumnHeadings
values will only add another underscore to every "Brand".
Sean

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!