Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Date Correction (2002)

    Hi,

    I'm importing information from multiple excel spreadsheets. The import is for a month at a time. For example, I've just imported May 2005. Depending on the accuracy of the person doing the data entry on the spreadsheet, the dates are often times "off" as in the year. For example, I've just import spreadsheets that have dates for May 2004 and May 1901. I'd like to set up an update query that will automatically update [service_date] to be sure the date year is the current year. I need to be sure that the month and day remain the same. I'm not sure if this is possible or how I would code the "update to" section of the query.

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Date Correction (2002)

    Note that you can still bump into issues with some of the more bizzare cases. For example, suppose someone enters 2/29/2004, but it should have been 2005. Access will balk at putting in a leap day in a year where it doesn't exist.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date Correction (2002)

    Actually, Access won't balk at this. It will "correct" the date to 3/1/05!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Correction (2002)

    Thanks Hans! I finally got it to work. I kept getting and error message. Was about to post back and thought I'd count parentheses fist. I put another one after Date() so it looked like Date()) and that did the trick.

    As always I appreciate the explanation!

    Thanks also for the other input from Wendell and Mark!

    Leesha

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Correction (2002)

    Oops, yes, I forgot one closing parenthesis. Sorry about that!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Correction (2002)

    Edited by HansV to add missing parenthesis

    You can create an update query with the following in the 'Update to' line for Service_Date:

    DateSerial(Year(Date()), Month([Service_Date]), Day([Service_Date]))

    The year is taken from the Date() function (=current date), while the month and day are taken from Service_Date.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Correction (2002)

    Not a problem!! I'm actually thrilled to finally be able t somewhat understand the syntax, although I'm a long way for totally getting it. No matter how much you guys have encouraged that I'll get it with time, learning / understanding how to write this stuff is harder than nursing school!!!

    Leesha

Posting Permissions

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