using CASE WHEN statements in Microsoft Access

Question

I know how to write CASE WHEN statements, but the problem here is translating them to Microsoft Access "language". I've tried with IF statements but it didn't work, I've looked up how to use CASE WHEN statements in Access but copying what I found didn't work, I'm clueless.. The following is the code I should translate:

SELECT clienti.nome, clienti.cognome

FROM ((clienti

INNER JOIN contiCorrenti ON clienti.ID = contiCorrenti.IDCliente)

INNER JOIN prestiti ON contiCorrenti.IBAN = prestiti.IBAN)

WHERE prestiti.durata < 

              CASE WHEN prestiti.rateizzazione = "mensile"

              THEN 60

              WHEN prestiti.rateizzazione = "annuale"

              THEN 5

              ELSE 0

              END;

June7 · Answer 1 · 2022-05-23T17:46:34.063

0

Access does not support CASE in SQL - use IIf() or Choose() or Switch().

durata < IIf(rateizzazione = "mensile", 60, IIf(rateizzazione = "annuale", 5, 0))

or

durata < Switch(rateizzazione = "mensile, 60, rateizzazione = "annuale", 5, True, 0)

Switch() and Choose() are VBA intrinsic function calls but IIf() is SQL so likely performs more efficiently.

edited May 23 '22 at 17:46

answered May 23 '22 at 17:37

June7

19,874
8
24
34

score 0 · Accepted Answer · answered May 23 '22 at 17:37

0

MS Access doesn't support case statements. Instead you can use the "immediate if" function (iif) and nest them for multiple conditions:

where prestiti.durata < iif (prestiti.rateizzazione = "mensile", 60,
                        iif (prestiti.rateizzazione = "annuale", 5, 0))

answered May 23 '22 at 17:37

Lord Peter

3,433
2
33
33

This works, thanks a lot! Weirdly I didn't come across any website that showed the immediate if function while looking for IF statement – dsdsds sdsds May 23 '22 at 17:46

using CASE WHEN statements in Microsoft Access

2 Answers2