Change a text field (YYYYMM) to a date value

[Chuck W]

Hello,
I extract data out of a system to a text file that gives me several fields
including a field called MonthCode. The values in this field are all six
digits (200812, 200901). I then import this into access and then change
the format to a number field with no problem. I need to change this to a
date field (12/1/2008, 1/1/2009). When I attempt to simply change it to a
date field I get the data value of 10/19/2449 for 200812. What is the best
way to make this change? I will have to do this often so I would like to
either automate it or write a write where the value appears automatically.

Thanks,

 

[John Spencer MVP]

Try the expression

DateValue(Format(200812,"####-##"))

As long as month code is a valid value and not null that should work.

If you might not have a valid value in the field then test with IsDate and
return null if the value cannot be converted.

IIF(IsDate(Format(200812,"####-##")),DateValue(Format(200812,"####-##")),Null)


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

 

[fredg]

Hello,
I extract data out of a system to a text file that gives me several fields
including a field called MonthCode. The values in this field are all six
digits (200812, 200901). I then import this into access and then change
the format to a number field with no problem. I need to change this to a
date field (12/1/2008, 1/1/2009). When I attempt to simply change it to a
date field I get the data value of 10/19/2449 for 200812. What is the best
way to make this change? I will have to do this often so I would like to
either automate it or write a write where the value appears automatically.

Thanks,

10/19/2449 sounds about right for a date value of 200812.

DateSerial(Left([MonthCode],4),Right([MonthCode],2),1)

will change your value to a valid date as of the first day of that
month.

You can use an update query to write the value to a [NewDateField]
(Date/Time datatype) field.

Update YourTable Set YourTable.NewDateField =
DateSerial(Left([MonthCode],4),Right([MonthCode],2),1)

or use that DateSerial() expression on a form or report (preceding it
with an = sign).