SQL Select Case to Access vba code

[Tupacmoche]

Hi VBA Masters,


I have SQL code with a Select Case for a column that I need to implement in an Access query as follows:


SELECT DISTINCT
c.CONTACT_DATE,
c.DESCRIPTION,
c.Purpose,
c.Contact_Type_DESC,
c.Id_Number,
Case
When m.MAIL_LIST_CODE = "MMS" THEN "Med_Contact"
Else "WSQ_Contact"
END as Contact_Author

FROM dbo_dm_contact AS c
left JOIN
(SELECT DISTINCT Id_Number,
MAIL_LIST_CODE
FROM dbo_Mailing_List
WHERE Mail_List_Code = "MMS") AS m
ON c.Author_id_Number = m.id_Number
Where Contact_Date >= DateAdd(month, - 18, GETDATE())


How can, I code/convert this portion so it works in an Access query?:banghead:


Case
When m.MAIL_LIST_CODE = "MMS" THEN "Med_Contact"
Else "WSQ_Contact"
END as Contact_Author

 

[theDBguy]

Hi. You could try using an IIf() statement to replace the CASE expression.

 

[The_Doc_Man]

In general, you would want to use IIF for a two-option alternative, or the SWITCH statement for a multi-option alternative.

https://www.techonthenet.com/access/functions/advanced/switch.php

https://www.techonthenet.com/access/functions/advanced/iif.php