Change Field Format using VBA

  • Thread starter Thread starter Arunpd
  • Start date Start date
A

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
 
Arunpd said:
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
Click to expand...

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
 
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

Allen Browne said:
Arunpd said:
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
Click to expand...

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
Click to expand...
 
Back
Top