Link to home
Start Free TrialLog in
Avatar of jmcclosk
jmcclosk

asked on

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

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?
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
Avatar of jmcclosk
jmcclosk

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It works perfectly!
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!