I have a MS Access database with 3 dates and 4 categories. I want to update the date according to category when the program opens. Category a=30 days, b=60 days, c=90 days and d=120 days. I want to search for any date < now and update according to the category. I can't seem to get the sytax correct on this to update. The second part of the problem is I want to move the old date to one of the other date fields. Here is an example of what I mean:
If nextcontact < now and category=a then
move nextcontact to lastcontact and
dateAdd("w", 30, now) to nextcontact
That is not the sytax used but to show you what I am trying to do. I need to do this for each category each time the program is opened. I would greatly appreciate any advice I can get.