Exporting report to Excel - formatting problems

[Guest]

I'm exporting a report to Excel and the format of some dates is getting
screwed up, not every date just some. How does this work and is it
correctible or is it a known bug?

 

[fredg]

I'm exporting a report to Excel and the format of some dates is getting
screwed up, not every date just some. How does this work and is it
correctible or is it a known bug?

You're there and we are here.
What specifically does 'screwed up' mean?

 

[Guest]

I lied due to incomplete information from a user Its not a date field, its
a field with 2 numerics followed by a blank followed by 2 numerics followed
by a blank followed by 2 numerics e.g. 99 99 99
Its getting mangled (spurious numbers inserted in string) in the output but
only some rows are mangled some are fine, no rhyme or reason why.
This only started happening after conversion from Office 2000 to 2003,
running db in 2000 mode.
- David

 

[Guest]

Here are some examples:
Report shows: 02 05 07
Excel shows: 31813

Report shows: 10 02 09
Excel shows: 40088

etc. etc.

 

[John Spencer]

Well, the numbers are day numbers. That is the number of days from
12/30/1899. If you apply a date format to them in Excel, you should see
the expected dates.

You could try wrapping the data in CDate or DateValue to force the
conversion. This may or may not work as expected - since you have not
specified "format" that these strings are stored as. mm dd yy, dd mm yy, yy
mm dd, etc.. Access will probably assume mm dd yy as the format.

 

[Guest]

the numbers have nothing to do with dates, its a 3-part version number.
I need to somehow get Excel to read it as a text string rather than a number.
The number is actually in format 99.99.99
Anyone know how? I've messed with the Format and Mask properties but nothing
works. You would think Microsoft applications could communicate ?!?

David

 

[John Spencer]

Sorry, you said in the first post "the format of some dates is getting
screwed up"

Have you tried pre-pending an apostrophe? That forces Excel to treat the
entry as text.
Chr(39) & [TheOriginalField]
Excel then will display 99.99.99
The Excel cell will contain '99.99.99