CASE...WHEN

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
 
D

Douglas J. Steele

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.
 
Top