exporting dates to Excel .. driving me crazy

[NOL]

Hi ,

I have a linked ORACLE table in Access 2000 with a date "mydate" of type "Date/time"

I have built a simple query which selects mydate.
the query output shows as mydate as e.g. "6/23/1997"

1. If I export this query to Excel using "output to" in a macro
the same date is displayed as "23-jun-97"

Although if i try to change it it reverts to the format "6/23/1997"
and then goes back to displaying "23-jun-97"


2. I tried using Format([mydate], "mm/dd/yyyy")
Then if i export it to Excel, i cannot sort or change the date format as it is now a text string instead of a date

3. I also tried CDate(Format([mydate], "mm/dd/yyyy"))
but it yields the same results as option 1.

4. I created a excel template ( which i really don't want to do 'cept as a last option) myfile.xlt and used it in the options for outputTo .
It does not seem to have any effect on the data.

Really need some help right now !
Thanks,
Gina.


bit

 

[llkhoutx]

Your getting the format of the displayed data confused with the format of the data itself. The data is exported in its natural format, not it's displayed format. If you want it to appear a certain way, convert it to text before exporting. You may have to reconvert it in Excel from text to a date if you're calculating with it.

 

[NOL]

Thanks !
But if i transfer it to Excel as text , i cannot change it fom text to date.
In fact I can't format it to any form at all.

I guess I'll have to live with the default display .
Unless any1 has other suggestions ?


Appreciate the help
Thanks
Gina

 

[llkhoutx]

You're still thinking incorrectly. One can convert a text date to a data date in Excel.

 

[AccessPhil]

Try changing the format in Excel. Select the column and format the cells the way you want it to appear. There are many date format in excel that already exist.

 

[NOL]

Well i did just that ,
but it would not allow me to change the date format of the exported field in Excel.

I re- created the query from scratch and this time
I rightcliek on the field in the query and defined format as "Short Date"
and now it works perfectly.

I output the query and i can change the format
The display is also the way I want it.

So everything's working fine now.
But i still can't explain what was wrong the first time .

Thanks for your help
Gina