Format as Date

[Sam]

Hi all
I have a table which stores a date like so: 06012004 (today UK). How can i convert this to a date
Many thank
Sam

 

[Allen Browne]

Create a query into this table.

Type an expression such as this into the Field row in query design:
DateSerial(Right([d], 4), Mid([d], 3, 2), Left([d], 2))

That assumes the text date is in a field named "d".

If you are trying to write the date to a true Date/Time field, change the
query to an Update query (Update on Query menu), and place the expression in
the Update row under your real date field. Then run the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can i convert this to a date?

 

[Sandra Daigle]

You could use the dateSerial Function:

Dim myDate As Date
Dim datefld As String
datefld = "06012004"

myDate = DateSerial(Right(datefld, 4), Mid(datefld, 3, 2), Left(datefld, 2))
Debug.Print myDate

Just replace datefld with a reference to the field or control that contains
the existing date. You can use this in a query or in VBA depending on what
you are planning to do with the converted value.

 

[Sam]

many thanks Allen that's worked perfectly!