CASE WHEN in MS Access SQL

Question

I'm writing a query using MS Access SQL and MS Access doesn't recognize my query.

So I want to have something like this:

If salutation = 1 Then
    salutation ='Mr'
ElseIf salutaion = 2 Then
    salutaion ='Mme'
ElseIf salutaion = 3 Then
    Salutation = 'Mlle'
Else
   Salution = 'ND'

And Then I entered the following query:

CASE WHEN (BN_CS_MP_MASTERPROFILE.CMP_SALUTATION='1') THEN 'Mr'
WHEN (BN_CS_MP_MASTERPROFILE.CMP_SALUTATION='2') THEN 'Mme'
WHEN (BN_CS_MP_MASTERPROFILE.CMP_SALUTATION='3') THEN 'Mlle'
ELSE 'ND' END

Any help will be welcome!

score 1 · Answer 1 · answered Feb 27 '19 at 13:27

Use switch:

SWITCH(BN_CS_MP_MASTERPROFILE.CMP_SALUTATION = '1', 'Mr',
       BN_CS_MP_MASTERPROFILE.CMP_SALUTATION = '2', 'Mme',
       BN_CS_MP_MASTERPROFILE.CMP_SALUTATION = '3', 'Mlle'
       1=1, 'ND'
      )

Note: If CMP_SALUTATION is declared as a number of any type, then remove the single quotes on the comparison.

score 1 · Answer 2 · answered Feb 27 '19 at 18:18

1

You can also use the Choose function in the following way:

Nz(Choose(BN_CS_MP_MASTERPROFILE.CMP_SALUTATION,'Mr','Mme','Mlle'),'ND')

answered Feb 27 '19 at 18:18

Lee Mac

15,615
6
32
80

score 0 · Answer 3 · answered Feb 27 '19 at 13:35

If your version/setup of Access does not support SWITCH, then consider using IIF instead:

IFF(BN_CS_MP_MASTERPROFILE.CMP_SALUTATION = "1", "Mr",
    IIF(BN_CS_MP_MASTERPROFILE.CMP_SALUTATION = "2", "Mme",
        IIF(BN_CS_MP_MASTERPROFILE.CMP_SALUTATION = "3", "Mlle", "ND")))

CASE WHEN in MS Access SQL

3 Answers3

Linked