CASE...WHEN

  • Thread starter Thread starter Desse
  • Start date Start date
D

Desse

I AM TRYING TO DO A CASE WITH VALUES STATEMENT IN A QUERY
AND IT KEEPS GIVING ME AN ERROR SAYING SYNTAX ERROR
(MISSING OPERATOR) IN QUERY EXPRESSION. ERROR 3075. FIND
BELOW SOMETHING SIMILAR TO WHAT I AM TRYING TO DO. HOW DO
I DO CASE STATEMENTS IN ACCESS SQL QUERIES. THANKS FOR
THE HELP

CASE
WHEN ( Left(MASTER.ADDRESS_1,7) = Left
(CurrCustStatus.ADDRESS,7) ) THEN "MATCH ON FIRST 7"
WHEN ( Left(MASTER.ADDRESS_1,10) = Left
(CurrCustStatus.ADDRESS,10) ) THEN "MATCH ON FIRST 10"
WHEN (Left(MASTER.ADDRESS_1,12) = Left
(CurrCustStatus.ADDRESS,12) ) THEN "MATCH ON FIRST 12"
WHEN (MASTER.ADDRESS_1 = CurrCustStatus.ADDRESS)
THEN "MATCH ON FULL ADDRESS"
ELSE "NO MATCH ON ADDRESS"
END
 
You could try nested IIf statements:

IIf(MASTER.ADDRESS_1 = CurrCustStatus.ADDRESS, "MATCH ON FULL ADDRESS",
IIf(Left(MASTER.ADDRESS_1,12) = Left(CurrCustStatus.ADDRESS,12), "MATCH ON
FIRST 12",
IIf(Left(MASTER.ADDRESS_1,10) = Left(CurrCustStatus.ADDRESS,10), "MATCH ON
FIRST 10",
IIf(Left(MASTER.ADDRESS_1,7) = Left(CurrCustStatus.ADDRESS,7), "MATCH ON
FIRST 7",
"NO MATCH ON ADDRESS"))))

BTW, please don't post all in capitals letters. It's the visual equivalent
of shouting, and it's considered to be quite rude.
 
Back
Top