Compare value before running update query

[browny]

Hi all
I want to view vehicles due for servicing in the next week. The queries giving me this information work fine. I have a form that I can select, from a drop down box, either 1,2,3 or 4. A report is then run and emailled to the appropriate people. After testing to see what was the easiest way to remember what the last week was (I tried setting the combo box to remember the value but it did not, unsure why, I also tried recording Mondays date in a field in a table and assigning each Monday a value 1-4 then running a query...long story short, to time consuming) I have used a table with one field to hold the value and after emailling the report, an update query adds one to this field and it is displayed above the combo box in a txt box. I want to reset the value to 0 if the value equals 4 before the update query runs. Is this the best way to do it or can someone suggest another way of doing this.
Thanks in advance
Craig

 

[grnzbra]

You've got it adding 1 to the existing value. How about an immediate if statement like

IIf(value = 4,0,value +1)

 

[browny]

Thanks grnzbra, have added it to the SQL statement and it works a treat. The code is below if it helps anyone else out. I changed the 0 to 1 so that it automatically jumps to the first week after the fourth.
UPDATE PM_Week SET PM_Week.WeekID = IIf([WeekID]=4,1,[WeekID]+1);
Craig