check box based on date calculation 1

[bledsoet]

Have an application were we are tracking a how long someone has been a member within our orginization. Would like to have an automated process where a check box is automatically selected when a specific date is reached.

ie., when someone has been a member for 5 years based on hire date, the check box for 5 years is automatically selected.

 

[PWise]

run this query
change tablename to your tablename
Fieldname to your Fieldname

update Tablename Set Fieldname = true
where Fieldname = False and DateDiff("yyyy", hiredate, now()) >=5

 

[PHV]

Warning: the DateDiff function may not always return the result you expect.
e.g. DateDiff("yyyy", #2006-12-31#, #2007-01-01#) = 1

So, a safer way:
UPDATE Tablename SET Fieldname = True
WHERE Fieldname = False AND hiredate <= DateAdd("yyyy", -5, Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886