But they ARE Equal... (1 Viewer)

2 fields, one in each table. Both fields are identical, short text, 255 char max. The join query on these two fields works perfectly 95% of the data, Access can tell that the join fields have identical data and joins the tables accordingly. But for a handful of people it just does not recognize that they are equal.

But again, I can see that this string of numbers matches (it's an account #). Now, one of the tables is an import from a raw data file, imported via an import spec. So I am sure that there must be something else in there. Does anyone have any ideas on where to go? It occured to me that perhaps this is similar to an issue I have had with numbers where I need to round numbers to 2 decimals because there are a couple of extraneous digits 45 0s to the right of the decimal.

Thanks!

P.S. I copied and pasted the data into excel, and excel recognizes the fields as equal.

I can see

Click to expand...

To your eye they look the same, a computer however has better vision:

A computer can see trailing spaces in strings-- "Like this one has "
It can also see newline characters, tabs and other characters that don't appear to human eyes.

I suggest doing a Trim() around the fields to get those "invisible" characters out of there. If that doesn't work, use the Len() function to determine the number of characters in each string. And then Asc() can tell you the exact character that is there but your eyes can't discern.

use Trim() function to both fields to remove excess space.
Maybe there are hidden Chr(0) on the field, remove them also.
use the Update Query to remove them:

Update yourTable Set Field1=Replace(Trim([Field1]), Chr(0), "");

I agree with plog. While you are debugging this, do a length test on the strings you are trying to compare. That will identify any trailing spaces.

going forward you maybe need to get the problem addressed in the app producing the excel results.

Great first experience on the boards! Thanks guys!!

Trim/Replace, etc worked like a charm!