Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Dateadd Function not working....

Discussion in 'Information Technology' started by Sheila D, Jul 28, 2006.

  1. Sheila D

    Sheila D
    Expand Collapse
    Guest

    Hi - I am using the Dateadd Function in an Update query to (try) to update
    dates by 4 years as follows
    DateAdd("yyyy",4,[LAST_CONTACT_DATE])
    This takes (for example) 2001 and updates to 2009 - can't understand why. If
    I then run again nothing happens at all. I can run with -4 and puts my dates
    back as they were but can't get the correct value and does nothing if I run
    it again. Am I missing something here..... here is the SQL
    UPDATE T_Contacts SET T_Contacts.NEXT_CONTACT_DATE =
    DateAdd("yyyy",4,[LAST_CONTACT_DATE])
    WHERE (((T_Contacts.NEXT_CONTACT_DATE) Is Not Null));

    Thanks for any help

    Sheila
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Sheila D" <SheilaD@discussions.microsoft.com> wrote in message
    news:308F0FBE-5A88-44E2-BD3B-803EA7936721@microsoft.com
    > Hi - I am using the Dateadd Function in an Update query to (try) to
    > update dates by 4 years as follows
    > DateAdd("yyyy",4,[LAST_CONTACT_DATE])
    > This takes (for example) 2001 and updates to 2009 - can't understand
    > why. If I then run again nothing happens at all. I can run with -4
    > and puts my dates back as they were but can't get the correct value
    > and does nothing if I run it again. Am I missing something here.....
    > here is the SQL
    > UPDATE T_Contacts SET T_Contacts.NEXT_CONTACT_DATE =
    > DateAdd("yyyy",4,[LAST_CONTACT_DATE])
    > WHERE (((T_Contacts.NEXT_CONTACT_DATE) Is Not Null));


    Are you sure you haven't made a mistake in your analysis of the
    situation? I don't see how that SQL could add 8 years instead of 4. I
    note that the SQL is dealing with two different fields,
    NEXT_CONTACT_DATE and LAST_CONTACT_DATE; that makes sense, but I'd like
    to make sure that's what you intend. Also, are you sure there aren't
    other updates being performed in code surrounding this one, such that
    the field gets updated twice?

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. Sheila D

    Sheila D
    Expand Collapse
    Guest

    Groan - how stupid of me, you're quite right Dirk, I'm using the wrong field
    to update by - must be the heat

    Sheila

    "Dirk Goldgar" wrote:

    > "Sheila D" <SheilaD@discussions.microsoft.com> wrote in message
    > news:308F0FBE-5A88-44E2-BD3B-803EA7936721@microsoft.com
    > > Hi - I am using the Dateadd Function in an Update query to (try) to
    > > update dates by 4 years as follows
    > > DateAdd("yyyy",4,[LAST_CONTACT_DATE])
    > > This takes (for example) 2001 and updates to 2009 - can't understand
    > > why. If I then run again nothing happens at all. I can run with -4
    > > and puts my dates back as they were but can't get the correct value
    > > and does nothing if I run it again. Am I missing something here.....
    > > here is the SQL
    > > UPDATE T_Contacts SET T_Contacts.NEXT_CONTACT_DATE =
    > > DateAdd("yyyy",4,[LAST_CONTACT_DATE])
    > > WHERE (((T_Contacts.NEXT_CONTACT_DATE) Is Not Null));

    >
    > Are you sure you haven't made a mistake in your analysis of the
    > situation? I don't see how that SQL could add 8 years instead of 4. I
    > note that the SQL is dealing with two different fields,
    > NEXT_CONTACT_DATE and LAST_CONTACT_DATE; that makes sense, but I'd like
    > to make sure that's what you intend. Also, are you sure there aren't
    > other updates being performed in code surrounding this one, such that
    > the field gets updated twice?
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     

Share This Page