SELECT CASE with MS Access ?

[dbelley_office]

Hi,

Is is possible to use the Select case in MS Access ?

If yes, what is wrong with this query.

SELECT NOPLAINTE, NOPOCKET, 
CASE PLAIDOYER 
WHEN 'Coupable' THEN '1'
WHEN 'Non coupable' THEN '2'
WHEN 'Bénéfice du doute' THEN '3'
END
FROM PLAINTES 
ORDER BY NOPLAINTE

Thanks.

[brucevde]

Jet Engine does not support Case When...

Use the IIF statement.

[AIS4U]

dbelly office:

The syntax of the Select Case statement is:

Select Case Expression
Case Expression1
Statement1
Case Expression2
Statement2
Case Expression_n
Statement_n
End Select


Private Sub cboMembership_AfterUpdate()
Dim strMembership As String

strMembership = [cboMembership]

Select Case strMembership
Case "Teen"
txtPrice = "$25"
Case "Adult"
txtPrice = "$50"
Case "Senior"
txtPrice = "$35"
End Select
End Sub


If you anticipate that there could be no match between the Expression and one of the Expressionn, you can use a Case Else statement at the end of the list. The statement would then look like this:

Select Case Expression
Case Expression1
Statement1
Case Expression2
Statement2
Case Expression3
Statement3
Case Else
Statement_n
End Select

[anguswalker]

To expand on #2, use
SELECT NOPLAINTE, NOPOCKET,
iif(PLAIDOYER = 'COUPABLE', 1 , IIF(PLAIDOYER = 'Non coupable' ,2 ,3)) AS PLAIDOYER
FROM PLAINTES
ORDER BY NOPLAINTE