Conditional field value in Access 2007

Question

I created a database to keep track of customer service related follow-ups in a call centre environment.
My table has, among the others, a Date created field, a Due date field and a Status field (Open, Due or Overdue).

I would like the value indicated in the Status field to update automatically as time elapses.

Can this be achieved and how?

Answer 1

This is absolutely possible. Why not? I would suggest you do the following:

(1) add another status to the status field called 'closed' (2) determine from a logorithmic perspective what 'Due' means (e.g. if the current date falls within 5 days of your due date (3) Write a query which updates your status either 'due' or 'overdue' depending on what the current date is

Answer 2

UPDATE tblDue SET tblDue.Status = "Due" WHERE (((tblDue.DueDate)=Now()));

UPDATE tblDue SET tblDue.Status = "Overdue" WHERE (((tblDue.DueDate)>Now()));