Changing DateTime Format to Short Date

[eilob]

Hi,
I have this module where I transfer an excel file into a table. The Date Submitted format is general date 1/2/2008 5:25:00 AM. I need to change this to a short date. So I was thinking adding code to the module specifying that when is transferred that the column is transferred in a Short Date Format.

Please if you could help me with this. The code below is where I transfer the file from Excel into a table called "TableSAExport".

Thanks in advance

Function ufnImportXLSA()

'Function instantiates Excel
'opens named files
'saves those files in current XL format
'closes the XL file
'then imports to named tables


'specific XL files to be processed

Const strFile1 = "C:\Export\Sales_Assistance.xls"

'define object variables for XL

Dim xlApp As Object
Dim xlWkb As Object

'instantiate XL and turn off Warnings

Set xlApp = CreateObject("Excel.Application")
xlApp.Application.DisplayAlerts = False

'open 1st workbook
'save in current version
'close workbook

Set xlWkb = xlApp.Workbooks.Open(Filename:=strFile1)
xlWkb.SaveAs Filename:=strFile1, FileFormat:=-4143
xlWkb.Close

'import XL file to named table

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableSAExport", strFile1, True


'clear workbook object

Set xlWkb = Nothing

Set xlApp = Nothing

End Function

 

[MajP]

All dates and time are saved the same way as an integer portion representing elapsed days and a decimal portion representing elapsed time since midnight.

All you need to do is put a format on the date where you display it; either in a form, report or in the table.

 

[MajP]

To see that all dates are saved the same way, but can be displayed any way you want.

?now()
3/11/2008 10:45:30 AM

?format(now,"short date")
3/11/2008

?format(now,"long date")
Tuesday, March 11, 2008

?format(now,"yyyy/mm/dd")
2008/03/11

?format(now,"hh:mm:ss")
10:48:06

etc.