Search code examples
sqlms-access-2003jet

How can I compare two tables if I have type mismatches in Access 2003?

I am comparing two table like this:

SELECT *
FROM tableA AS A INNER JOIN tableB AS B ON B.ID = A.ID
WHERE A.col1 <> B.col1 OR A.col2 <> B.col2 OR ...

This query doesn't work because I have a type mismatch somewhere (and Access doesn't tell me which columns don't match).

Is there an efficient way to find which columns don't match? Is there a way to ignore the mismatch and compare anyways?

edit: I tried casting everything to strings using Cstr (inspired by Tuck's answer), but Access says I have an Invalid use of Null in there. Does that mean that Cstr is returning a Null? If so, I tried nz(Cstr(...), 0) and that doesn't help either.

edit: Moving the call to nz inside the call to Cstr got rid of that Invalid use of Null and solved the problem: Cstr(nz(..., 0))

Solution

  • I'm not sure if Access supports CAST() operations but in your WHERE clause you can try:

    WHERE CAST(A.col1 AS VARCHAR(100)) <> CAST(b.col1 AS VARCHAR(100))

    If varchar isn't supported, use a type that Access does support.

    For your first pass, wrap all your column comparisons with the CAST() operation. Once that works, remove the CAST() operations one by one from your column comparisons until you find the pairing that doesn't work.

    Hopefully someone with better Access knowledge can provide you with a faster solution.