Crosstab values shift with TransferSpreadsheet (A2K)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote