IIF Function in Access SQL

Question

I am trying to write an IIF statement to return on certain Job Titles in my Query.

However, when I went to run my code, this column did not even get returned in the table that I created. I looked online and it seems that this is the correct syntax. Am I missing something that is causing it not to return this column?

IIF ([w.Job_Title] = "Sales Pro" OR [w.Job_Title] = "Services Pro" OR [w.Job_Title] = "Universal Pro" OR [w.Job_Title] = "Fulfillment Pro", "JobTitle", NULL),

I originally had a WHERE statement at the end of my query, but this seemed to cause my other data to not be accurate, so I thought this IIF way would be better.

Below is the Full query:

SELECT cal.FiscalYear,
       cal.FiscalQuarter,
       cal.FiscalMonth,
       cal.FiscalWeekNumber,
       Format(w.Day, "Dddd") AS DayOfWeek,
       cal.Day AS CalendarDay,
       w.Day,
       c.SegStart,
       c.SPLITNAME AS SkillName,
       w.Site,
       w.Campaign,
       w.Manager,
       w.Supervisor,
       IIF ([w.Job_Title] = "Sales Pro"
            OR [w.Job_Title] = "Services Pro"
            OR [w.Job_Title] = "Universal Pro"
            OR [w.Job_Title] = "Fulfillment Pro", "JobTitle", NULL),
       w.EmployeeNumber AS SalesID,
       w.ACDID,
       c.CallID,
       w.Employee,
       c.Caller_Name AS cms_EmployeeName,
       c.Answerer_Name AS cms_ProEmployeeName,
       c.Answered,
       c.AcwTime,
       c.Agt_Released AS AgentReleased,
       c.AnsHoldTime,
       c.AnsLogin,
       c.Calling_Pty AS CallingParty,
       c.Conference,
       c.ConsultTime,
       c.Dialed_Num AS DialedNumber,
       c.DispIVector,
       c.DispSplit,
       c.DispVDN,
       c.Duration,
       c.Disposition,
       c.Held,
       c.HoldABN AS AbanOnHold,
       c.OrigLogin,
       c.QueueTime,
       c.RingTime,
       c.TalkTime,
       c.Transferred,
       c.VDN2,
       c.VDN3,
       c.VDN4,
       c.VDN5
FROM (t_wfm AS w
      INNER JOIN t_cms AS c ON (c.CALLING_PTY = w.ACDID)
        AND (Format(c.SEGSTART, "Short date") = Format(w.[Day], "Short date")))
INNER JOIN tbl_Calendar AS cal ON cal.Day = w.Day;

Can you provide a small sample of data and expected results? — G Cadogan
I am expecting the Job Titles to only return those above. In the sample of data, this column is not returned at all. — Jenna Terral
How to ask a good SQL question. Sample data and expected result is much more useful than a textual description. -- Also please post the full query. — Andre
The query is just one to retrieve data from two tables, the only thing I am trying to get it to do is filter down to just retrieving certain Job Titles — Jenna Terral

Andre Andre · Accepted Answer · 2018-07-24T15:03:11

You haven't provided a column alias.

The column is most probably there, but with a title like Expr1 or something.

I think what you want is retrieving the actual title, not the constant "JobTitle":

   IIF ([w.Job_Title] = "Sales Pro"
        OR [w.Job_Title] = "Services Pro"
        OR [w.Job_Title] = "Universal Pro"
        OR [w.Job_Title] = "Fulfillment Pro", [w.Job_Title], NULL) AS JobTitle,

Edit

If you want to only return the rows containing these titles, the check goes into the WHERE clause. The IIf() is not needed then.

WHERE ([w.Job_Title] = "Sales Pro"
        OR [w.Job_Title] = "Services Pro"
        OR [w.Job_Title] = "Universal Pro"
        OR [w.Job_Title] = "Fulfillment Pro")

IIF Function in Access SQL

1 Answers