file type export problem

[Guest]

I'm exporting a REPORT from Access 2003 to Excel 2003. I have one field in
Access, defined as text, that won't export correctly. Some records export as
text, but most export as an 8 digit negative number with 2 decimal places.
This field is from a sub-table in the report. All other fields from both the
main and sub tables export correctly.

Any suggestions?
thx, Dick

 

[Pete D.]

Did you create an export specification to declare field as text? Pete D.

 

[Guest]

How is that done? I have 2 Access manuals, neither of which mention it.

I set up a trial report using the problem table as the main table. The field
in question did the same thing when I exported it. It's defined as text with
25 characters. The data is alphanumeric.

Dick

 

[John Nurick]

Hi Dick,

This is less a problem with Access than with the way Excel interprets
the data.

Try using a calculated field in the report with an expression that
prepends an apostrophe to the value of the field you are exporting,
e.g.

="'" & [MyField]

The apostrophes should force Excel to treat the values as text and not
try to interpret them as numbers. They do not show up on the
worksheet.

On Thu, 23 Aug 2007 08:36:04 -0700, Dick F <Dick

 

[Guest]

Thanks for the info, but it didn't work. I've tried many things and have
concluded that it's something with the Access field def. I can use your idea
and substitute any other field name and it will work (sort of), but not with
this particular field. I get #error in the report. This field won't work in
any calculated expression that I've tried.

I said 'sort of' because when I cat a hyphen in front of a field it WILL
show up in Excel.

 

[John Nurick]

Try exporting from Access to a text file, then importing the text into
Excel.

Thanks for the info, but it didn't work. I've tried many things and have
concluded that it's something with the Access field def. I can use your idea
and substitute any other field name and it will work (sort of), but not with
this particular field. I get #error in the report. This field won't work in
any calculated expression that I've tried.

I said 'sort of' because when I cat a hyphen in front of a field it WILL
show up in Excel.

Hi Dick,

This is less a problem with Access than with the way Excel interprets
the data.

Try using a calculated field in the report with an expression that
prepends an apostrophe to the value of the field you are exporting,
e.g.

="'" & [MyField]

The apostrophes should force Excel to treat the values as text and not
try to interpret them as numbers. They do not show up on the
worksheet.