• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

Export from access to excel

Hi,

I have an access-table with many fields with a time format hh:nn:ss.
With a query i select the records that i want.
When i do an export to excel with code :
DoCmd.TransferSpreadsheet acExport, 8, stDocName, Pad, True, ""
then i get in excel 00:01/1900 08:30 and i see 00/01/1900.
What i want is to see 08:30.
When i export the table then everything is ok.
I have set the format to "Long Time" but that gives also the wrong result.

How can i solve this?

Tx
0
sonmic
Asked:
sonmic
1 Solution
 
als315Commented:
You can convert time to text before export or simply change format of cell in Excel (set it to h:mm)
0
 
peter57rCommented:
You can force a format in your query...

MyTime:format(Timefield, "hh:nn:ss")

but this will be passed as text into Excel
0
 
Jeffrey CoachmanMIS LiasonCommented:
Is the table data actually a Date/Time datatype, ...or is it Text.?
Because a "Time" will generally include a "date" of some sort.

I usually get out the hammer and create a Numeric (Double datatype) field in a query form the "Time" field, ...and export that to Excel, then you should be able to format the Excel sheet for "Time" and get back the correct format.

*However*, you should be able to just set the Format for that filed in Excel to "Time" to get the same time format is you had in Excel.
0
 
Jeffrey CoachmanMIS LiasonCommented:
This DB works fine for me and produces an Excel like the one attached (again, you will have to format the field in Excel for "Time")
Database22.mdb
YourTable.xls
0
 
aikimarkCommented:
If these fields are truly date fields, you might export a query instead of a table.  In the query, you would format the column to hours and minutes.
Example:
Select FORMAT([My Date Column Name], "hh:nn") 
From [My Table Name]

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now