SELECT CASE with MS Access ?

Hi,

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

If yes, what is wrong with this query.

Code:

---

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.

Jet Engine does not support Case When...

Use the IIF statement.

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

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