Formats in Exporting Access Reports to Excel

[Clare]

Help. I am going insane.
I have to export reports to excel. one column is job grades 04,05,...07, 8A,8B,9A,9

there is a text column label. everythng comes in as text in excel with leading zeros, etc. EXCEPT 8A and 9A which are converted to .33 and .38.

Access XP, Excel XP.

 

[John Nurick]

Hi Clare,

Somehow Excel is interpreting 8A as the time 8:00 am and 9A as the time
9:00 am, and then displaying these as decimal numbers.

Here's something to try. In the textbox in the report that displays this
field, I guess the controlsource is now just the name of the field, e.g.
JobGrade
Change this to
"'"&[JobGrade]
so the output looks like this
'04
'8A
etc. With luck the apostrophes will disappear when the report is
exported, but will prevent Access from misinterpreting some of the
grades as times.

Please let us know whether it works.

 

[Herbert Chan]

How about changing the field type to Text if Clare does not need to do any
math with that field?

Herbert

Hi Clare,

Somehow Excel is interpreting 8A as the time 8:00 am and 9A as the time
9:00 am, and then displaying these as decimal numbers.

Here's something to try. In the textbox in the report that displays this
field, I guess the controlsource is now just the name of the field, e.g.
JobGrade
Change this to
"'"&[JobGrade]
so the output looks like this
'04
'8A
etc. With luck the apostrophes will disappear when the report is
exported, but will prevent Access from misinterpreting some of the
grades as times.

Please let us know whether it works.

Help. I am going insane.
I have to export reports to excel. one column is job grades 04,05,...07, 8A,8B,9A,9B

there is a text column label. everythng comes in as text in excel with leading zeros, etc. EXCEPT 8A and 9A which are converted to .33 and .38.

Access XP, Excel XP.

 

[Clare]

John,

You are right - it is treating them like 8 AM and 9 AM! Bizarre. The help is no help at all. I put the single quote prefix on and that keeps them as text, if you search and replace, my levels with a 0 as char 1 convert to number which isn't so terrible. If my users print the report though it doesn't look great, and they have to search and replace the single quote out in excel. I wanted to saty with one version of the report for printing and for exporting

I can't find anything in options in excel where I could suppress that

Thanks for responding

 

[John Nurick]

How about prefixing them with a single space " "? This will be less
conspicuous - in fact invisible when the report is printed, if you
right-align the textbox that displays them.

I don't think there's any way of fixing this in Excel once they've been
interpreted as numbers.

Having tried this for myself just now (Office XP) and got the same
effect with 8A and 9A, I discovered that Excel treats apostrophes
unusuallly in this situation too. Normally, an apostrophe at the
beginning of a string signals Excel to treat it as text (this is a
left-over from the earliest days of spreadsheets) and isn't actually
displayed on the worksheet (or exported, for that matter). But the
apostrophes in the report were showing up. You can make them behave as
normal (i.e. appear in the formula bar but not in the worksheet) but
doing something like this in Excel VBA:

Dim C as Excel.Range
For Each C in Application.Selection.Cells
C.Formular = C.Formula
Next

 

[dp]

Help. I am going insane.
I have to export reports to excel. one column is job grades 04,05,...07, 8A,8B,9A,9B

there is a text column label. everythng comes in as text in excel with

leading zeros, etc. EXCEPT 8A and 9A which are converted to .33 and .38.

Access XP, Excel XP.

Excel doesn't read all of the imported rows before determining what the
format for the cells should be. You'll find that if the sort order is
re-arranged so that the 8A/9A appears within the first few rows, the column
will be formatted as text.
dp