I created a database to keep track of customer service follow-ups in a call centre environment. My single table has, among others, a Due date field and a Status field (values: 'Open', 'Due' and 'Overdue').
I want to run an update query to automatically change the values indicated in the Status field to 'Open' when the current date is less than the Due date, to 'Due' when the current date is equal to the Due date, and to 'Overdue' when the current date is past the Due date.
I do not know how to write the expression(s) to achieve that result and in which cell(s) to enter the code. Can someone please provide assistance?
Use a Switch Function expression which compares Due date with today's date,
Date()
, and returns the appropriate Status.Change your query view mode from Design View to SQL View, paste in the following
UPDATE
statement text, and replace YourTable with the name of your table.I didn't want to try to describe how to do that in Design View. But after you add the statement text in SQL View, you can return to Design View to see how it's displayed there.
Note, to keep Status current, you would have to run that
UPDATE
daily. And maybe that's satisfactory. However you could simply derive Status when needed in aSELECT
query using the sameSwitch
expression. That way Status would always be current without the need toUPDATE
values stored in the table.