Reformat date 10106=Jan 1,06

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

Could some please help me to get the following date into a usable format?
The dates are currently formatted as: 10106-Jan 1st, 2006; 110206-Nov
1st,2006 etc.

I've used the Left and Right functions to pull off the year and day, but
can't get the month due to the 1 or 2 digit issue. It is a number field and
I'm using Access 2003.

Help please. Thanks.

Maria
 
Change it to a text field and add the leading zero using an update query.
Right("0"&[YourField],6)
Then you can parse it.

You should go ahead and add a DateTime datatype field and update the new
field with the parsed date.
 
Maria said:
Hi.

Could some please help me to get the following date into a usable format?
The dates are currently formatted as: 10106-Jan 1st, 2006; 110206-Nov
1st,2006 etc.

I've used the Left and Right functions to pull off the year and day, but
can't get the month due to the 1 or 2 digit issue. It is a number field and
I'm using Access 2003.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use this:

CDate(Format(Left(col_name, InStr(col_name,"-")-1),"00-00-00"))

Substitute the name of the column for "col_name."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKwzJIechKqOuFEgEQI2kACffkzydWJeUuziziRxV2nAQiQe5OwAoKeL
4ri6wTd+c9e7MOJgDeJa/7bj
=xZU9
-----END PGP SIGNATURE-----
 
Back
Top