Thread: Text to Date conversion

Ok, but you have to add a new field that is DateTime datatype.
Full_Date: IIf(DateSerial(Left("20" & Right("0" &
[YourNumField],4),4),Val(Right("20" & Right("0" &
[YourNumField],4),2))+1,0)<#1/1/2005# Or
Val(Right([YourNumField],2))>20,#12/31/2004#,DateSerial(Left("20" & Right("0"
& [YourNumField],4),4),Val(Right("20" & Right("0" &
[YourNumField],4),2))+1,0))

If the number would produce a date less than 1/1/2005 or greater than
1/1/2020 it changes it to 12/31/2004.

--
KARL DEWEY
Build a little - Test a little


"Bobbye R" wrote:

> I have a table with thousands of records that use the following format for
> dates and I'd like to convert all to month end dates. (Presently it's a
> numeric field) For example:
>
> 709 (is actually 9/2007). I would like it to read 9/30/2007
> 1001 (is actually 01/2010). I would like it to read 1/31/2010
> etc
>
> None of the fields are null but I did find some where the number makes no
> sense for a date like 895. For those I'd like to date them all 12/31/2004
> just so I won't have any blank fields.
>
>
> --
> Access 2007 User, Thanks in advance Bobbye

Worked perfectly. Thanks a million
--
Access 2007 User, Thanks in advance Bobbye


"KARL DEWEY" wrote:

> Ok, but you have to add a new field that is DateTime datatype.
> Full_Date: IIf(DateSerial(Left("20" & Right("0" &
> [YourNumField],4),4),Val(Right("20" & Right("0" &
> [YourNumField],4),2))+1,0)<#1/1/2005# Or
> Val(Right([YourNumField],2))>20,#12/31/2004#,DateSerial(Left("20" & Right("0"
> & [YourNumField],4),4),Val(Right("20" & Right("0" &
> [YourNumField],4),2))+1,0))
>
> If the number would produce a date less than 1/1/2005 or greater than
> 1/1/2020 it changes it to 12/31/2004.
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Bobbye R" wrote:
>
> > I have a table with thousands of records that use the following format for
> > dates and I'd like to convert all to month end dates. (Presently it's a
> > numeric field) For example:
> >
> > 709 (is actually 9/2007). I would like it to read 9/30/2007
> > 1001 (is actually 01/2010). I would like it to read 1/31/2010
> > etc
> >
> > None of the fields are null but I did find some where the number makes no
> > sense for a date like 895. For those I'd like to date them all 12/31/2004
> > just so I won't have any blank fields.
> >
> >
> > --
> > Access 2007 User, Thanks in advance Bobbye

Bobbye R wrote:
> I have a table with thousands of records that use the following format for
> dates and I'd like to convert all to month end dates. (Presently it's a
> numeric field) For example:
>
> 709 (is actually 9/2007). I would like it to read 9/30/2007
> 1001 (is actually 01/2010). I would like it to read 1/31/2010
> etc
>
> None of the fields are null but I did find some where the number makes no
> sense for a date like 895. For those I'd like to date them all 12/31/2004
> just so I won't have any blank fields.

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

Perhaps these (run them in the order presented):

This one puts in the default date:

UPDATE table_name
SET date_column = "12/31/2004"
WHERE Val(Right$(date_col,2)) NOT BETWEEN 1 And 12

This one puts in the "real" date:

UPDATE table_name
SET date_column = DateSerial(Left$(Format(date_column,"0000"),2),
Right$(date_column,2)-1, 0)
WHERE Len(date_column)<=4
AND Val(Right$(date_col,2)) Between 1 And 12

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSbB8VYechKqOuFEgEQJGdwCghSXxnxYIIzef8st1ZPXbHF mKUzYAoJ2s
UXRJ/nr3EdebChWGTG797l4U
=4Foz
-----END PGP SIGNATURE-----