?
Solved

Export from access to excel

Posted on 2013-01-07
5
Medium Priority
?
463 Views
Last Modified: 2013-01-17
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
Comment
Question by:sonmic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38750425
You can convert time to text before export or simply change format of cell in Excel (set it to h:mm)
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38750577
You can force a format in your query...

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

but this will be passed as text into Excel
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38751426
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38751477
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
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 38757035
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question