Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    18

    Unhappy Unanswered: Help with update query

    Does anyone know how to change the year in a date field? I have a Doctor table and in my field DATESCANNED I have dates (from a prior data conversion - before my time) that read M/D/10 that is not the correct year.

    I would appreciate any help.

    Karen

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by klrclark
    Does anyone know how to change the year in a date field? I have a Doctor table and in my field DATESCANNED I have dates (from a prior data conversion - before my time) that read M/D/10 that is not the correct year.

    I would appreciate any help.

    Karen
    What do you want it changed to???
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jun 2004
    Posts
    18
    the correct year there is another field called PDATE that is the actual date the employee saw the doctor that would be the correct year that the form was scanned into the system.

    Thanks,

    Karen

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    UPDATE SomeTable SET DATESCANNED=DatePart(DATESCANNED,"m") & "/" & DatePart(DATESCANNED,"d") & "/" & DatePart(PDATE,"yyyy") ;
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jun 2004
    Posts
    18
    Thank you for the help but I'm getting a type conversion failure and my dates in the DATESCANNED field are being deleted.

    Any ideas?

    Thanks in advance.

    Karen

  6. #6
    Join Date
    Jun 2004
    Posts
    18

    Please Help with this query

    UPDATE Doctor SET DATEREAD = DatePart("m",[DATEREAD]) & "/" & DatePart("d",[DATEREAD]) & "/" & DatePart("yy",[PDATE])
    WHERE DATEREAD between #1/1/10# and #12/31/10#;


    I get an error that states a type conversion error occurred? All of the dates in the DATEREAD field are deleted that meet this where statement requirement.

    I would appreciate any help.

    Thanks,
    Karen

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by klrclark
    UPDATE Doctor SET DATEREAD = DatePart("m",[DATEREAD]) & "/" & DatePart("d",[DATEREAD]) & "/" & DatePart("yy",[PDATE])
    WHERE DATEREAD between #1/1/10# and #12/31/10#;


    I get an error that states a type conversion error occurred? All of the dates in the DATEREAD field are deleted that meet this where statement requirement.

    I would appreciate any help.

    Thanks,
    Karen
    OOPS!!! Try: UPDATE Doctor SET DATEREAD = CDate(DatePart("m",[DATEREAD]) & "/" & DatePart("d",[DATEREAD]) & "/" & DatePart("yy",[PDATE])) ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jun 2004
    Posts
    18

    Thumbs up

    thank you for all of your help!

    I finally realized what I was doing wrong. Access sees a date as dd/mm/yyyy. I just needed to rearrange the update statement to get it to work.

    Thanks a million for your help.

    Karen

Posting Permissions

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