Date format problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sam - 30 Mar 2005 02:01 GMT

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?

Signature

Thanks

Reply to this Message

Ken Snell [MVP] - 30 Mar 2005 02:09 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Hi,
> I have some code behind a form that basically deletes data from a temp
[quoted text clipped - 8 lines]
> Can anyone tell me how to keep the original 00:00 format in the exported
> file?

Reply to this Message

sam - 30 Mar 2005 02:19 GMT

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:
[quoted text clipped - 14 lines]
> > Can anyone tell me how to keep the original 00:00 format in the exported
> > file?

Reply to this Message