title: how do I write a select statement that will evaluate multiple fields to populate a new field value?
body: <p>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:</p>
  <pre class="lang-none prettyprint-override"><code>LogonID
  DeptName
  CertDate
  CertStatus
  PastDue
  </code></pre>
  <p>I need to write a query that will provide a Status field with the appropriate value where:</p>
  <pre class="lang-sql prettyprint-override"><code>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’)
  </code></pre>
  <p>Anything else is blank</p>