Date turns to a number

[dbl]

Hi I have a text field in a report with =Date() as the control source and
format as medium date, the problem I have is when I send the report to Excel
it turns the date into a number how do I stop this happening?

Thanks Bob

 

[Douglas J. Steele]

Just format the cell as a Date.

Under the covers, VBA dates are eight byte floating point numbers, where the
integer portion is the date as the number of days relative to 30 Dec, 1899
and the decimal portion is the time as a fraction of a day. Today is 27 Oct,
2007: if you ask Access for Format(Date, "0"), it will return 39382. If you
plug that number into Excel and tell it to format it as a date, you'll again
get 27 Oct, 2007.

 

[dbl]

The only problem with formatting the cell in Excel is getting someone to
remember to do it. Isn't there a different way of doing it?

 

[Douglas J. Steele]

You could always use Automation to format Excel so that no one has to
remember. I showed an introduction to the topic in my July, 2005 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html

Another possibility (untested) is that you use the Format function in the
report's underlying RecordSource, rather than simply setting the Format
property of the text box.