time exports to Excel as 00/01/1900 (1 Viewer)

[krowe]

Hi

I am trying to export a table from access to excel. It has a couple of columns with times in and when excel opens it just shows 00/01/1900 for all the values.

Here is my query that creates the table for exporting:

SELECT tblRoadClosuresAdditionalDates.Reference, tblRoadClosuresAdditionalDates.DateOfClosure, CStr(Format([TimeFrom],"Medium Time")) AS TimeFromMod, TimeValue([TimeTo]) AS
TimeToMod, tblRoadClosuresAdditionalDates.NumberOfPeople INTO tmpAdditionDates
FROM tblRoadClosuresAdditionalDates
WHERE (((tblRoadClosuresAdditionalDates.Reference)=[Forms]![frmProgressRoadClosures]![sfrmRoadClosuresAdditionalDates].[Form]![Reference]));

You can see a couple of my attempts to handle this data above.

I am using this code to create my excel table - dont know if this is relevant.

DoCmd.OpenQuery "qryRCADditionalDatesFilteredExport"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tmpAdditionDates", "xxxxxxxxxxxxxx", No

Can anybody suggest a way to handle this field so that I get just the time in the medium time format appearing in the excel table.

Thanks

Kev

 

[namliam]

Time is nothing but a date and date is nothing but a double with a format on it.

Hence, the date you are seeing is because you have a date format on a time field, try giving the field a proper time format like: H:MM:SS

 

[krowe]

Hi

Thanks for your quick reply.

The format of that field in the table is set to "short time" (it defaults to this when I put in HH:MM

In this table the data appears to be just the time as I expect. Also, in the query qryRCADditionalDatesFilteredExport it appears as time, and it appears as time in tmpAdditionDates, but when i export it the spreadsheet is formats as date for some reason.

Regards

Kev

 

[krowe]

Looking at this more, it is the full DATETIME being exported to excel, but it only shows the date, unless i format the relevant cells as HH:MM. Im thinking the way around this may be to create a second spreadsheet and link to the export, this way I can enforce the formatting I want...

 

[Keith Tedbury]

You can format the column it goes into using vba

I assume that the xxxxxxxxxxxxxxxxxxxxx is the path to where you are saving the spreadsheet. You run the code to do the formatting after the export.

DoCmd.OpenQuery "qryRCADditionalDatesFilteredExport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tmpAdditionDates", "xxxxxxxxxxxxxx", No

dim x As Object

Set x = CreateObject("Excel.application")
x.Workbooks.Open filename:=xxxxxxxxxxxxxxxxxxxxx
x.Range("A:A").NumberFormat = "hh:mm:ss;@"

x.ActiveWorkbook.Save
x.Visible = True
Set x = Nothing

replace A:A with the column that it goes into, its a range so A:A would be column A you can have A which would format columns A to D.

Make sure you have the excel reference selected under references