Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab column name problem (2003 / SP2)

    A crosstab query of mine is changing text contained in a field that is used as the column heading, or pivot field. When the crosstab query runs, if a period, Chr(46), is contained in the field then it appears as an underscore character (_) in the output.
    This could potentially be more than a minor nuisance if I were to allow this output to be maintained or updated and then want to use this information to update its original source.
    I haven't found any other character besides the period that this problem occurs with, but before I attempt some sort of workaround I'm curious if I should be on the lookout for any other built-in character transformations. Has anyone else seen this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab column name problem (2003 / SP2)

    Field names in Access may not contain the following characters: . (period), ! (exclamation mark), [ and ] (square brackets), and non-printing characters such as a line feed.
    So when you have a text field as column header in a crosstab query, Access will replace these characters with an underline character to create valid column headers. Also, leading or trailing spaces will be suppressed.

    Note: a crosstab query is never updateable, so I'm not sure how you'd want to update the original data from it.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab column name problem (2003 / SP2)

    Thanks, yes that makes sense, that the values become field names in the output.

    <Note: a crosstab query is never updateable, so I'm not sure how you'd want to update the original data from it.
    > Yes, I realize this, but I am exporting the crosstab output to Excel and had thoughts of permitting users to update the information there and then I would build a routine to import the revised worksheet back into Access.

    Anyway, thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •