0

I need to compare two tables inside a Microsoft Access database.

Since this is a one time thing, I didn't want to look for a software to do that, instead I wanted to do that by creating queries to show me the rows that only exist in one of the tables.

So I created two queries, query 1 is showing me the rows of table 1 that are not present in table 2 and query 2 is showing me the rows of table 2 that are not present in table 1.

In both queries I do a LEFT JOIN/RIGHT JOIN on all of the primary key columns and only return the rows of one table, when the primary key columns in the other table are NULL.

This worked the way I expected it, but for one row.

Checking the content of this one row in both tables I couldn't find a difference, there were no leading or tailing spaces, there were no TABs instead of spaces and I couldn't find other differences of that kind, believe me I tried.

Because I didn't know what else to do, I just replaced some non-ansi characters (german umlauts ä and ü) with their ansi counter parts, and suddenly my queries showed no differences anymore. Replacing the ansi characters back to their non-ansi counter parts one by one, I found out, that the JOINs don't seem work when there's an "ä" present in the joined column. If the column contains an "ü" instead of an "ä" (same place in the string), then the JOINs work.

How is that possible? Is that a known bug of Access? Or is it a feature? How can I rely on working JOINs? Is there a setting I have to change?

Nostromo
  • 1,177
  • 10
  • 28
  • Check the binary content of the string. ü can be 0308 COMBINING DIAERESIS + a normal U, or 00FC LATIN SMALL LETTER U WITH DIARESIS. These are different, the first even takes 2 more bytes to store in Access), so won't get recognized as identical, even though they display as identical. – Erik A Jun 04 '20 at 13:50
  • How do I check the content of a string column on binary level in Access? – Nostromo Jun 04 '20 at 14:06
  • Check [this answer](https://stackoverflow.com/a/51949328/7296893), the UnicodeToByteArray function. You can adjust that to just return the bytes as hex. – Erik A Jun 04 '20 at 14:09
  • I got both strings (the ones with the umlauts) out of the tables in a VBA module and compared them there... they are considered equal. Nevertheless they are not considered equal in the `JOIN`. – Nostromo Jun 05 '20 at 05:12
  • That's very strange, if they're fully identical (byte-for-byte), it should just work. Alternatively, if they're truly identical, you can perform a binary comparison: `ON STRCOMP(Field1, Field2, 0) = 0`. Note that you should not paste unicode in VBA (either the immediate window or a module) to check if they're identical, since the VBE doesn't support unicode and thus casts the characters to ANSI (which does allow umlauts depending on your locale). – Erik A Jun 05 '20 at 05:19

0 Answers0