Change field value based on current date?
In my membership database each member has a status ("Member Current" or "Member Lapsed") stored in a calculation field. What I want is for the value to change based on the expiry date. Thus, once their membership lapses, say, on the 31 July 2012, then the membership status field would changes from "Member Current" to "Member Lapsed". The calculation I've tried looks like this:
If (MembershipExpiry ≥ Get(CurrentDate); "Member Current" ; "Member Lapsed")
What I've discovered is that the field does not update automatically when the date changes. It will change when I test it by manually changing the expiry date to later than the current date, but it wont update automatically once that expiry date has passed.
Please tell me there is some way of doing this?
Thank you for your help.