Date format problem

[sam]

Hi,
I have some code behind a form that basically deletes data from a temp
table, runs a query to append new data and exports it as an .xls file. Two
fields are data type date/time and are set as short time (throughout the
database) however the export formats the time of for example 15:23 as
01/01/1900 when viewed in the .xls file
I don't know if this is where the problem is but the code I am using to
export is:
DoCmd.TransferSpreadsheet acExport, 8, "tblProgramExport", "C:\Data\" &
Me.txtExportFile, True, ""
Can anyone tell me how to keep the original 00:00 format in the exported file?

 

[Ken Snell [MVP]]

Create a query that is based on tblProgramExport table. In the query, put a
calculated field in place of the time field:
MyTimeField: Format([TimeFieldName], "hh:nn")

Export the query.

 

[sam]

Thanks Ken, I also didn't realise I would have to set the data type to text
in the table to keep the format. Thanks for your reply.

Create a query that is based on tblProgramExport table. In the query, put a
calculated field in place of the time field:
MyTimeField: Format([TimeFieldName], "hh:nn")

Export the query.

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have some code behind a form that basically deletes data from a temp
table, runs a query to append new data and exports it as an .xls file. Two
fields are data type date/time and are set as short time (throughout the
database) however the export formats the time of for example 15:23 as
01/01/1900 when viewed in the .xls file
I don't know if this is where the problem is but the code I am using to
export is:
DoCmd.TransferSpreadsheet acExport, 8, "tblProgramExport", "C:\Data\" &
Me.txtExportFile, True, ""
Can anyone tell me how to keep the original 00:00 format in the exported
file?