Change Field Format using VBA

[Arunpd]

Hi,

I would need to import an Excel file into an Access table everyday.The Data
field gets imported in the format mm/dd/yy hh:mm:ss format.I need to change
this into mm/dd/yy format only using a VBA program.Please help

Regards
Arun

 

[Allen Browne]

I would need to import an Excel file into an Access table everyday.The
Data
field gets imported in the format mm/dd/yy hh:mm:ss format.I need to
change
this into mm/dd/yy format only using a VBA program.Please help

Do you want to:
a) remove the hours, minutes, and seconds (leaving just the date),
or
b) suppress the display of the hours, minutes, and seconds, but leave them
in the field.

If (a), execute an Update query, e.g.:
Dim strSql As String
strSql = "UPDATE [MyTable] SET [MyField] = Fix([MyField]) " & _
"WHERE [MyField] Is Not Null;"
dbEngine(0)(0).Execute strSql, dbFailOnError

If (b), use DAO to assign a value to the Format property of the Field in the
TableDef. The basic reference is:
Currentdb.TableDefs("tblPage").Fields("PageCreated").Format
However, you will need to create the property if it does not exist.
SetPropertyDAO() is a custom function to do that:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO

 

[Arunpd]

Hi,

Thanks everyone for the help..

I wanted option (a) to completly delete the hours/mins/secs.I will try the
update query.

Regards
Arun David

I would need to import an Excel file into an Access table everyday.The
Data
field gets imported in the format mm/dd/yy hh:mm:ss format.I need to
change
this into mm/dd/yy format only using a VBA program.Please help

Do you want to:
a) remove the hours, minutes, and seconds (leaving just the date),
or
b) suppress the display of the hours, minutes, and seconds, but leave them
in the field.

If (a), execute an Update query, e.g.:
Dim strSql As String
strSql = "UPDATE [MyTable] SET [MyField] = Fix([MyField]) " & _
"WHERE [MyField] Is Not Null;"
dbEngine(0)(0).Execute strSql, dbFailOnError

If (b), use DAO to assign a value to the Format property of the Field in the
TableDef. The basic reference is:
Currentdb.TableDefs("tblPage").Fields("PageCreated").Format
However, you will need to create the property if it does not exist.
SetPropertyDAO() is a custom function to do that:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO