Dateadd Function not working....

[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

 

[Dirk Goldgar]

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?

 

[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

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)