Exporting to Excel files

  • Thread starter Jonathan Scott via AccessMonster.com
  • Start date
J

Jonathan Scott via AccessMonster.com

I am currently working on an Access97 database application, and when I
export a table to an Excel spreadsheet, I find that my dates turn out as
other than I would expect or want.

For instance, "00/01/01" will sometimes be displayed "00/01/01", sometimes
as "01-Jan-00" or something similar.

What I need is a way to force ALL dates to be exported to spreadsheet
format in the format "YYYY/MM/DD". Is there a way to do this, efficiently?
Or perhaps I will have to recreate my table with the date fields as text
and put the dates in there as I expect them? (a rediculous idea considering
how wasteful and illogical it is)

Any help with learning to export to formats such as Word97 or Excel97, etc.
would be much appreciated.

TIA,
Jonathan Scott
 
J

Joan Wild

Jonathan said:
I am currently working on an Access97 database application, and when I
export a table to an Excel spreadsheet, I find that my dates turn out
as other than I would expect or want.

For instance, "00/01/01" will sometimes be displayed "00/01/01",
sometimes as "01-Jan-00" or something similar.

The display is just that - a format for display purposes. Dates are stored
as numbers
Today's date is 38412 (the number of days since Dec 30, 1899). The decimal
portion of the number indicates the time, as a portion of the day. So right
now it's 38412.53

It doesn't matter how you format it, the underlying value is what is stored
(both in Excel and Access).

So just open your Excel worksheet after the export, select the column and go
to Format, Cells and format it to your liking. You can even choose a custom
format of "yyyy/mm/dd".
 
J

Jonathan Scott via AccessMonster.com

Thanks Joan! I found that I can control the output if I also control the
format in Access. I couldn't find the formatting rules that I can apply to
dates, etc. in an Access table, so I was a bit confused. I did the typical
"YYYY/MM/DD" and it worked in Access and Excel, following your example.

Thanks again,
Jonathan Scott
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top