Text to Date conversion

[Bobbye R]

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.

 

[KARL DEWEY]

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.

 

[Bobbye R]

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

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

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.

 

[KARL DEWEY]

You need to change -- Or Val(Right([YourNumField],2))>20,
to read --- Or Val(Right([YourNumField],2))>12,

This is testing if month value greater than 12, not greater than year 2020
as I said before.
--
KARL DEWEY
Build a little - Test a little

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

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

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.