Multiple criteria update query in Access

Question

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?

Answer 1

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.

UPDATE [YourTable] AS y
SET y.Status =
    Switch
        (
            y.[Due date] > Date(), 'Open',
            y.[Due date] = Date(), 'Due',
            y.[Due date] < Date(), 'Overdue'
        );

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 a SELECT query using the same Switch expression. That way Status would always be current without the need to UPDATE values stored in the table.

SELECT
    y.[Due date],
    Switch
        (
            y.[Due date] > Date(), 'Open',
            y.[Due date] = Date(), 'Due',
            y.[Due date] < Date(), 'Overdue'
        ) AS [Status]
FROM [YourTable] AS y;