using CASE WHEN statements in Microsoft Access

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;

>Solution :

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

Leave a Reply