IIF syntax in MS Access

LuckyLucks
LuckyLucks used Ask the Experts™
on
Hi:


 I have two tables in Access that I want to compare each of the two columns in both tables and if they match output the third column in the 2nd table. I am trying to do the below but it complains of syntax error

Select A.*
       , IIF(A.col1=B.col2, (A.col2=B.col2, B.col3,"No match"),"No match") as Final_val
from A, B;

Also, B is a lookup table so not sure if I have better options than a cartesian join.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
General Manager
Commented:
SELECT IIF(A.col1=B.col2, (IIF(A.col2=B.col2, B.col3,"No match")),"No match") as Final_val from A, B

You are missing the SELECT statement if you are going to append the AS Result
I think you are also needing to have a complete IIF nested statement as well to make it work.

If you have an example DB you can attach with the two tables to experiment with, that would be helpful in figuring this out.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial