CODEWITHSUNDEEP
sqlms-access
Minonja Nandraina Razakasoa
Minonja Nandraina Razakasoa

Reputation: 101

CASE WHEN in MS Access SQL

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!

Upvotes: 0

Views: 10265

Answers (3)

Lee Mac
Lee Mac

Reputation: 16015

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

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522119

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")))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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.

Upvotes: 2

Related Questions