How to use case with inStr RRS feed

  • Question

  • SELECT Company_name, left(company_Address,  CASE WHEN instr(company_Address,",") -1) = 0 THEN LEN (company_Address) ELSE instr(company_Address,",") -1 END)
    FROM Company;

    I am trying to use case with inStr. what is the correct syntax for CASE in Access

    Thursday, January 9, 2020 11:12 PM

All replies

  • Maybe you can use the IIf function?

    -Tom. Microsoft Access MVP

    Thursday, January 9, 2020 11:55 PM
  • Unless Access SQL has changed, there is no CASE. IIF is the closest thing to it. It takes 3 arguments:

    • The expression to test
    • the response if true
    • the response if false

    It can be nested as well like this:

    SELECT IIF(myField="Door", "Red", IIF(myField="Wall", "Blue", "Black"))

    Bill Mosca
    https://wrmosca.wordpress.com https://groups.io/g/MSAccessProfessionals

    Friday, January 10, 2020 4:30 PM