Update Query to add or change data

[Keith]

I have several fields in a database that I need to change. The orginal date
in the filed "DOB" Date of birth was entered with only a 2 digit year. Such
as 6954 or 101254 which should be 06/09/1954 or 10/12/1954. So I need to
insert the two digits 19 into these fields and change them to a Date/Time.
It is currently as a number field (Double). I have the same problem with
another field that would require 20 to be added where the last two digits are
00 or higher such as 05. Can anyone help with this??

 

[John Spencer]

And what date is represented by 11154? November 1 1954 or January 11 1954?

Another example 10188.

I am going to assume that this is in Month Day Year order and that 5 digit
numbers represent the months 1 to 9 and 6 digit numbers represent month 10
to 12

TEST the following formula

DateSerial(DOBMod 100,DOB\10000, Dob\100 Mod 100)

Did I mention that you should TEST this on a copy of your data.

DateSerial will use two digit years to generate the century. 30 and higher
will generate 1900s. 29 and lower will generate 2000s. That is based on
the cutoff you've set in your operating system.

 

[John Spencer]

Whoops, missed a space and had second thoughts

DateSerial(DOB Mod 100, DOB\10000, DOB\100 Mod 100)

Also I would put criteria in the update query that made sure the number was
Between 10101 And 123199

I also worry about how you handled dates in the year 2000. Did those get
stored as 3-digit or 4-Digit numbers
January 1, 2000 --> 000101 --> 101
November1,2000 --> 001101 --> 1101

So maybe better would be the following which forces a string with 6
characters

DateSerial (Left(Format(DOB,"000000"),2),
Mid(Format(DOB,"000000"),3,2),Mid(Format(DOB,5,2)))

 

[Keith]

John

Thanks, I will try these. To answer your question, 11154 could be either.
But down the road it will be verified! The year 2000 would be 1101 for
1/1/2001. All or MDY none are 3 digits minimum 4. The problem would be as
your example 11154 could be either 11/1/54 or 1/11/54 again I can get those
verified.

I will try it on a test table first. Anything else your can think of would
be greatly appreciated!