Wrong number of Argument in iif

JAMES125
JAMES125 used Ask the Experts™
on
I tried to use the following code in Access 97 to compare the same column from 2 different tables.  When I try to run it, I get Wrong number of arguments error.

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t2.[MANUFACTURER NAME], '', t2.[MANUFACTURER NAME])) = ''
UNION ALL SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t1.[MANUFACTURER NAME], '', t1.[MANUFACTURER NAME])) = ''
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
wrong parenthezis:

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t2.[MANUFACTURER NAME]), '', t2.[MANUFACTURER NAME]) = ''
UNION ALL SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t1.[MANUFACTURER NAME]), '', t1.[MANUFACTURER NAME]) = ''

Author

Commented:
Thanks, that fixed the error but it didn't work the way I'd hope.  It still returns records with null values in both tables i.e. if both table have a null value in the same field I do not want to see that record.  Is this possible?

Commented:
You need this:

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE t2.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
UNION ALL SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE t1.[MANUFACTURER NAME] = t1.[MANUFACTURER NAME];
You can modify the query to verify that your local name is not null - and use the IsNull() function to streamline your code as well, e.g.:

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE IsNull(t2.[MANUFACTURER NAME])
     AND NOT(IsNull(t1.[MANUFACTURER NAME]))
UNION ALL
SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE IsNull(t1.[MANUFACTURER NAME])
     AND NOT(IsNull(t2.[MANUFACTURER NAME]))

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