Reformat Date Field

S

shairal

I receive a file that has the date formatted as 27-DEC-2008, 5-JAN-2009, etc.
How do I convert that to a text field of 20081227, 20090105?
 
J

John Spencer

CDate(TheField)
or
DateValue(TheField)
Those will both convert a valid date string to a dateTime value

Then you can use the format function to force a string in the format you
want

Format(DateValue(TheField),"yyyymmdd")

And if you need that as a number use the Val function to convert the
string to a number
VAL(Format(DateValue(TheField),"yyyymmdd"))



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
S

shairal

Thanks John! Works perfect!

John Spencer said:
CDate(TheField)
or
DateValue(TheField)
Those will both convert a valid date string to a dateTime value

Then you can use the format function to force a string in the format you
want

Format(DateValue(TheField),"yyyymmdd")

And if you need that as a number use the Val function to convert the
string to a number
VAL(Format(DateValue(TheField),"yyyymmdd"))



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Click to expand...
 
Top