Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing year part of a date field?

    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

  2. #2
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing year part of a date field?

    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.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing year part of a date field?

    Thanks Jeff!! Worked great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •