convert from number to date format

[Guest]

I import CSV file and saved it as an Access database. However, I could only
save date field as numbers, eg '20051023'. How can I convert this number
field into a date field in Access?

 

[Arvin Meyer [MVP]]

Write a query:

UPDATE MyTable SET MYNewField = CDate(Mid([MyNumberField],5,2) & "/" &
Mid([MyNumberField],7,2) & "/" & Left([MyNumberField],4));

Where MyTable is your table, MyNewField is a new date/time field and
MyNumberField is your "date field"
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Van T. Dinh]

Since "yyyymmdd", is not a standard date format, Access won't recognise it
as a date value and would import as numeric (Long), perhaps.

* Open your imported Table in Design View and add a new Field [NewDate]

* Create a run an Update Query with the SQL

UPDDATE [YourTable]
SET [NewDate] = DateSerial( Left([OldDate], 4), Mid([OldDate], 5, 2),
Right([OldDate], 2) )

* (if you want), open your Table in Design View, delete the Field [OldDate]
and rename the Field [NewDate] to [OldDate]. Make sure the values in
[NewDate] are OK first!