I am trying to create an update query in Access using the Month function of a date field and adding or subtracting months from it but start getting into problems where I am adding months and it takes me over 12.


UPDATE Subscription SET Subscription.sub_due = "R1"
WHERE (((Month([renewal_date])) Between [PubMonth]+3 And [PubMonth]+5) AND ((Subscription.discount) Not In (100,33)) AND ((Subscription.language)=[Wire Language]) AND ((Subscription.sub_type)="newsletter") AND ((Year([renewal_date]))>=Year(DateValue(Now()))));
With a renewal date in October month should evaluate to

10+3 ===> 1 Plus increment of Year by 1

10+5 ===> 3 Plus increment of year by 1

Can anyone suggest a way of doing this?

Thanks.

Talat