Auto Value Changes in a field

[MMWC]

I currently run an application in Excel which is now too RAM heavy to remain
fast and efficient. There is much functionality built into this Spreadsheet
which is dead easy to do – just get into the cell of your choice and go crazy
with expressions statements etc. So much so, there is very little actual VBA
written into the application.

Now I am trying to get the same functionality into Access, a different
animal and very clunky to the uninitiated. I was told that I would not need
to use much VBA in Access either, because there are “ways†to get around it.
So I am keen to see this and ask the Access cohorts out there to please show
me the light.

This particular thread requires that my Tracking field must updated itself
very time the date limits are breached – example:
Assuming that the action is no closed / completed (filter)
If my [Target_date] field is greater than the current [Date] + 14 Days the
[Tracking] field must set itself to “On Trackâ€
If my [Target_date] field is less than the current [Date] + 14 Days the
[Tracking] field must set itself to “Due < 14 Daysâ€
If my [Target_date] field is less than the current [Date] + 0 Days the
[Tracking] field must set itself to “Overdueâ€

The Excel function I put up for this reads like:
"=IF(OR(F8="TBP",F8=""),"",IF(OR(D8="Closed",D8="Completed"),"Disposed",IF(AND(D8="On
Hold",F8-$E$5<0),"Holding",IF(AND(D8<>"On
Hold",F8-$E$5<0),"Overdue",IF(F8-$E$5<14,"Due <14 days","On Track")))))"

Which is the easiest way to achieve this result (via Query, field
expression, function, macro, combination, etc) please?

 

[KARL DEWEY]

Try this --
Tracking: IIF([Target_date] > Date() +14, "On Track", IIF([Target_date]
Between Date() AND Date() +14, “Due < 14 Daysâ€, “Overdueâ€))