Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab values shift with TransferSpreadsheet (A2K)

    I am exporting a crosstab query to an Excel spreadsheet using the TransferSpreadsheet action in a macro. Although the totals crossfoot, many of the the values in the cells are shifted over one or more fields.

    I thought that perhaps punctuation in the the field names may have something to do with it, but I can't see a pattern. I thought of creating a temporary table from the crosstab and then exporting that, but crosstabs don't like to make tables )-:

    A sample of the field names:
    Adjustments Jax - 213366 Adjustments Tpa 8136 American Express ATM Dep Verif Ft Laud - 1749 ATM Dep Verif Jax - 1758 ATM Dep Verif Tpa - 1632 BankCard (By Site)
    Any ideas?

    Thanks,

    Randy

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab values shift with TransferSpreadsheet (A2K)

    Excuse me? Is everything between the hyphens a field name or is the whole thing a field name? I would suggest you try the make table route. There's no problem creating a table from a crosstab, but you have to save the crosstab and then
    use it as the record source in your MakeTable query.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab values shift with TransferSpreadsheet (A2K)

    Charlotte,

    Thanks for the reply, and sorry about the confusion on the fields, there are many there but came accross differently. Anyway, I even tested by changing the field names to a,b,c,d,e,f... ect. and tried to export it to every flavor of Excel, with the same results - the information in the fields crossfoots, but is not in the correct fields. Strange thing is that I can copy the entire table and paste to Excel manually, and it's fine!

    I need some Access Headache Pills....

    Thanks,

    Randy

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab values shift with TransferSpreadsheet (A2K)

    What do you mean, it is not in the correct fields? It can't really be anywhere else, but the *order* of the fields may not be what you expect. Is that what you're seeing? If so, have you tried putting in column headings in the cross tab query? That will control the order of the columns.

    The problem you may have with exporting to Excel is that Excel, or at least the converter, doesn't necessarily recognize empty columns, so it may shift data to where it thinks it belongs or you may find that your columns no longer line up, with extra cells inserted into a row by the converter. Watch out for double quotes and commas within fields. Excel sees double quotes as text delimiters and commas as field delimiters, so you may wind up with things like ABC, Inc broken into two separate fields. This usually isn't a problem with Access exports, but watch out for it all the same.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SOLUTION Crosstab values shift with TransferSp

    I just stumbled on the answer, and am posting it in case someone else needs it, now or later. If one is manually exporting to Excel from Access 2000, an option in the dialog box called

Posting Permissions

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