Auto update based on date conditions

[Webb]

What am I doing wrong?


If the dtRTC_DUE is not past due then I want to auto update my Priority
field to 1
If the dtRTC_DUEis less than 7 days past due I want it to auto update my
Priority code to 2
If the dtRTC_DUE is 7 days past due, but less than 14 days past due, the
Priorty Code should update to 3
If the dtRTC_DUEis 15 days past due, but less than 21 days past due, the
Priority Code should update to 4
If the dtRTC_DUE is greater than 21 days past due, the Priority Code should
update to 5

or if the Med Expiration Date is within the 14 days, I want the Priority
Code to update to 5. How do I do this?

This doesn't work. Should I have it in the After Update Properties of
Priority? Or, is it all wrong?

If Me.RTC_DUE= or <Now()+6 Then Me.Priority=1
f Me.dtRTC_DUE=Now() +6 And Me.dtRTC_DUE< Now() +7 Then Me.Priority = 2
If Me.dtRTC_DUE =Now() + 7 And Me.dtRTC_DUE < Now() + 21 Then Me.Priority =
3
If Me.dtRTC_DUE = Now() + 14 And Me.RTC_DUE < Now() + 21 Then Me.Priority =
4
If Me.dtRTC_DUE =Now() + 21 Then Me.Priority = 5

Thanks for any assistance/clues.
LaDonna

 

[Damian S]

Hi LaDonna,

If you are trying to set a default value in your priority field that can
then be updated by your user, then having code in the after update event of
your date field would be appropriate. If you are talking about having the
priority update every day, then you are flirting with danger attempting to
store this value - ie: you would be better off having an unbound field that
is calculated based on the date.

That being said, here is the format you require, assuming that the date due
is considered "due" if it's today's date:

If Me.RTC_DUE > Now() Then
Me.Priority=1

elseif (Me.dtRTC_DUE < Now()) and (Me.dtRTC_DUE >= (Now() -7)) Then
Me.Priority = 2

elseIf (Me.dtRTC_DUE < (Now() - 7)) And (Me.dtRTC_DUE >= (Now() -14)) Then
Me.Priority = 3

elseIf (Me.dtRTC_DUE < (Now()- 14)) And (Me.RTC_DUE >= (Now() - 21)) Then
Me.Priority = 4

else
Me.Priority = 5
end if


Hope this helps.

Damian.

 

[Webb]

Works great! I used an unbound field calculated based on date as you
suggested.

Could I add another condition to calculate the Priority code in addition to
the conditions below?
For example, I want the Priority to auto update to a Priority 5 if the
RTC_DUE date is <= 14 days of the Med_Expiration date. When I add the
condition to those below I get errors.
LaDonna