Wrong Numbers when transferred to Excel

[Martin Allaire]

Hi! Hopefully you can help me. I have a simple db with text and fixed numbers fields that are shown in an Access report. When I pull the report the numbers are good on the print preview, however when I do an "analyze with excel" some of the numbers come up wrong on the spreadsheet. (eg: "03-070" becomes "25628"). Any clue how to fix this?

 

[MyndPhlyp]

Hi! Hopefully you can help me. I have a simple db with text and fixed

numbers fields that are shown in an Access report. When I pull the report
the numbers are good on the print preview, however when I do an "analyze
with excel" some of the numbers come up wrong on the spreadsheet. (eg:
"03-070" becomes "25628"). Any clue how to fix this?

"25628" might be a date displayed as a general number. It just happens to
come out to March 1, 1970 (3/01/70 which closely resembles "03-070").

Check the format of the column containing "03-070" to ensure it is a text
field.

Also, in Excel, to force a number to display as text, prefix the number with
an apostrophie (i.e., '03-070).

 

[Martin Allaire]

Hi MyndPhlyp
Thanks for the advice. Unfortunately the properties are really set to text in Access and I can't select in Excel the properties BEFORE doing the transfer with "Analyze using Excel". I don't know if there is a way to set an excel template with the properties pre-set and tell Access to drop the content of the report in that template

The apostrophe is the only tip that works fine but then it creates other problems because the data is not in the format that is needed for other internal reports.

 

[MyndPhlyp]

Hi MyndPhlyp,
Thanks for the advice. Unfortunately the properties are really set to

text in Access and I can't select in Excel the properties BEFORE doing the
transfer with "Analyze using Excel". I don't know if there is a way to set
an excel template with the properties pre-set and tell Access to drop the
content of the report in that template.

The apostrophe is the only tip that works fine but then it creates other

problems because the data is not in the format that is needed for other
internal reports.

ROFL. You mean users can't be just a little tolerant about the content of
their reports? Geez, it's just a little apostrophe!

It sounds as though either Excel or Access is trying to be a little too
smart - most likely Excel. Is it possible to format the "03-070" style text
strings to something else? (Ah, but those pesky end users intolerant of an
apostrophe are probably going to have an absolute fit if the hyphen isn't in
the string.)

Just skimming through the online help, it looks like it might be possible to
use the OutputTo action to achieve similar results. (On the Index tab of the
online help, enter the word "OutputTo" and select "OutputTo Action" from the
list.) There appears to be a setting for a template that you might use to
control the formatting of columns in the spreadsheet.

I haven't tried any of this, so I can't speak with authority. Just
spitballing some ideas.

 

[MyndPhlyp]

Thanks MyndPhlyp,
I will try that and if it doesn't work well my big baby users will have to

live with the '

ROFLMAO. Just remember, if it weren't for users, our lives would be so much
more simplified!

 

[cabong]

You know you can control Excel through VBA code in Access?
Depending on the complexity of the report you could fire
Excel from code and use ADO to transfer your info

 

[cabong]

You know you can control Excel through VBA code in Access?
Depending on the complexity of the report you could fire
Excel from code and use ADO to transfer your info

 

[Martin Allaire]

Thanks Cabong
VERY unfortunately my knowledge of VBA is limited to knowing how to spell it :-(