Improve company productivity with a Business Account.Sign Up

x
?
Solved

When exporting data from Access to Excel, Time Field is blank

Posted on 2009-05-08
5
Medium Priority
?
235 Views
Last Modified: 2013-11-27
I am using the DoCmd.TransferSpreadsheet command to transfer 16 data items in a single row of an Access table to the spreadsheet of an Excel Workbook (Headers from A1:P1, Data from A2:P2).  All data is properly exported to Excel except for the field "TIME", which holds the time the incident occurred in HH:MM "LL" ("LL" standing for AM or PM).  The field header exports fine to cell C1, but the cell where the actual time value should be, C2, is completely empty.  Any idea why only this one piece of data would not be exported?
0
Comment
Question by:jmcclosk
  • 3
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24338741
try creating a query against the table,
convert the content of the field to text  cstr([nameofField]) , save the query
then use the query for export instead of the table
0
 

Author Comment

by:jmcclosk
ID: 24338820
I tried this, but when I changed the TransferSpreadsheet command to use the query instead, I got this error:
Run-time error '3417':
An action query cannot be used as a row source.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 24338881
why are you using an acction query, just do a simple select query
0
 

Author Closing Comment

by:jmcclosk
ID: 31579575
It works perfectly!
0
 

Author Comment

by:jmcclosk
ID: 24339014
Sorry!  That was a bone-headed move on my part.  I re-used the query originally created to make the table, but forgot to change the type.  Thanks for your help!
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

595 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