how do I write a select statement that will evaluate multiple fields to populate a new field value?

Question

I’ve been rummaging through a bunch of postings, but haven’t found one that fits my issue. I’ve tried CASE and IIF, but none seem to work. My wife would say I don’t know how to Google correctly. I have an export loaded into both SQL Server Express 15 and Access 16 (365). I can use either to accomplish my goals. I have a table imported from a data export with these fields:

LogonID
DeptName
CertDate
CertStatus
PastDue

I need to write a query that will provide a Status field with the appropriate value where:

If ((CertDate Is Not Null) AND (CertStatus = ‘Current’) AND (PastDue = ‘No’)) then
    Status = ‘ReadOnTime’)

If ((CertDate Is Not Null) AND (CertStatus = ‘Current’) AND (PastDue = ‘Yes’)) then
    Status = ‘ReadLate’)

If ((CertDate Is Null) AND (CertStatus = ‘Pending’) AND (PastDue = ‘Yes’)) then
    Status = ‘UnReadLate’)

Anything else is blank

Answer 1

In SQL Server you would use the CASE expression.

I used the field names from your sample and you did not provide a table name so you will have to update the example to fit your needs.

SELECT CASE WHEN [CertDate] IS NOT NULL
                 AND [CertStatus] = 'Current'
                 AND [PassDue] = 'No' THEN 'ReadOnTime'
            WHEN [CertDate] IS NOT NULL
                 AND [CertStatus] = 'Current'
                 AND [PassDue] = 'Yes' THEN 'ReadLate'
            WHEN [CertDate] IS NULL
                 AND [CertStatus] = 'Pending'
                 AND [PassDue] = 'Yes' THEN 'UnReadLate'
            ELSE ''
       END AS [Status]
FROM   [YourTableName];