How to convert into date format?

Question

Using Access 2003

Date Column datatype is text

Table1

Date

20090528
20090529
20090530
20090502
20090504

Expected Output

28-May-2009
29-May-2009
30-May-2009
02-May-2009
04-May-2009

...,

How to make a query for the Expected Output Date format?

Answer 1

CDate should do what you want...

Answer 2

As VBA code - you could wrap it as a function

Dim strMyDate As String
Dim dteDate As Date

strDate = "20090528"
dteDate = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))
MyStr = Format(dteDate, "dd-mmm-yyyy")
Debug.Print MyStr

As a Data type in a Table - If you append the data to a table where the field is Date/Time formatted then you can specify the format on the Form / Table i.e. at output time.

Answer 3

I find that Cdate does not work for me.

Format(DateSerial(Left(Field1, 4), Mid(Field1, 2, 2), Right(Field1, 2)), "dd-mmm-yyyy")