Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update field where dates are = in two other fields (2000)

    I have a very simple update query that has been a PITA since I came in to work. I want to compare two fields, if the dates in them are equal, update a third field to say "Yes". SQL below:

    UPDATE tblPersonnel SET tblPersonnel.WEOIF = "YES"
    WHERE (([oif_from]=[we_from]));

    The fields are formatted mm/dd/yyyy (since SQL is so <img src=/w3timages/censored.gif alt=censored border=0> picky)... I know there are values where this is true...I'm looking at them.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Update field where dates are = in two other fields (2000)

    Could it be that one or both of the date fields actually hold date+time values? You wouldn't see that because of the mm/dd/yyyy format.
    If oif_from = 08/13/2005 10:03 AM and we_from = 08/13/2005 3:58 PM, they aren't considered equal, even though both are displayed as 08/13/2005.

    BTW the formatting of fields in the table is not relevant to SQL.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field where dates are = in two other fields (2000)

    >>BTW the formatting of fields in the table is not relevant to SQL.

    Then where is it?

    I'll check to see if they're date/time, but I'm almost positive they're not....the military usually doesn't care what time, just what day. If they are date/time, how do I change it to just date?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field where dates are = in two other fields (2000)

    Just checked.... <img src=/S/nope.gif border=0 alt=nope width=15 height=15>, no times.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Update field where dates are = in two other fields (2000)

    The formatting is important if you include a date in an SQL statement itself. For example,

    UPDATE tblPersonnel SET tblPersonnel.WEOIF = "YES"
    WHERE [oif_from]=#01/08/2005#

    Although 01/08/2005 means the 1st of August in the UK and in The Netherlands, it will be interpreted as the 8th of January by SQL even on a UK or Dutch system.

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

    Re: Update field where dates are = in two other fields (2000)

    We'll have to know more about the data, then, or see the database.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update field where dates are = in two other fields (2000)

    Hans, for some reason when I first withdrew the data from an externa data source and saved it to excel, it was formatted as date/time. When I imported into access and changed the format, it looked like it parsed the time off. (I checked it by changing the format to general date...nothing showed up). I went back into the original excel document, changed the format in the imported cells, re-imported the data, and viola! it worked.... thanks for your help, and the info on SQL.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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