Changing year part of a date field?

Topic

New Reply

I’m looking for suggestions on how to change all of the dates in one of my queries to the 19th century instead of the 20th century. There are about 3000 or so.
All the birth dates that in the system that were before 1930 are showing up as for example 2020 which should be 1920. I have changed all the year fields to require that a 4 digit year is entered and now I need to change all the 20th century birth dates to 19th century. Any ideas would be great!
Thanks
Don
Access 97 SR-2

Reply | Quote

Replies

The following code will update the DOB (date of birth) field on a table called tbldemographics, where the DOB is greater than today. Your regional settings MUST have the date set to mm/dd/yyyy for this to work. Make a copy of the database, then run this against the copy. NEVER RUN ANY UN-TESTED UPDATES AGAINST PRODUCTION. (Sorry for shouting, I just can’t say that enough. I’m on Access 97 SR2.

jeff

UPDATE TblDemographics SET TblDemographics.DOB = Left([TblDemographics]![DOB],6) & “19” & Right([tblDemographics]![dob],2)
WHERE (((TblDemographics.DOB)>Date()));

ps, some wise-acre out there is probably going to point out to you that the 19th century starts 1801, the 20th century starts 1901, and the 21st century starts 2001. Alas, some wise-acre has.

Reply | Quote

Thanks Jeff!! Worked great!

Reply | Quote