How to convert a number to a date...

[ThriftyFinanceGirl]

Does anyone know how to convert a number (which is not really a number), such
as 10202003 and convert it to a date "10-20-2003"? In a query or in code
doesn't matter, I just don't have a clue as a normal conversion sees 10202003
as something totally different.

 

[John Spencer]

You might try the following (assuming there is always a value in SomeField)
DateSerial(Right([SomeField],4),Left([SomeField]2),Mid([Somefield],5,2))

Another option

IIF(IsDate(Format([SomeField],"@@-@@-@@@@")),CDate(Format([SomeField],"@@-@@-@@@@")),Null)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

CDate(Format(10202003, "00-00-0000"))


You may want ot check first if
CDate(Format(10112003, "00-00-0000"))


produces the intended day and month (10th of November or 11th of October)


Vanderghast, Access MVP

 

[Daryl S]

This will convert the text string to a true date. You can use this in a
form, query, report, code, etc. The format the date will be displayed in
will be determined by your system settings, or if you choose to format it in
your report, query, form, etc. Put this code in a module. It works
assuming any single-digit months and days have their leading zeros (e.g.
01012009).

Public Function MMDDYYYYToDate(strMMDDYYYY As String) As Date
'Convert a string variable in MMDDYYYY format to a true date

MMDDYYYYToDate = DateSerial(Right(strMMDDYYYY, 4), Left(strMMDDYYYY, 2),
Mid(strMMDDYYYY, 3, 2))

End Function