Joins failing due to mixed case values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know how to handle keys with mixed case ? ...

I have an alphanumeric [AccountNo] key that uses mixed case, such that the
field values are supposed to be differentiated by case as well. For example,
the following two records have similar keys except for the case of the last
"c" or "C".

AccountNo CompanyName
1234ABC InfoNauts
1234ABc Big Daddys

When I join Company and Contact tables, I get repeated values because access
seems to treat 1234ABC and 1234ABc as the same value (for joins).

How can I make it so the joins are respectful of the differences ??

THANK YOU !

John
 
ACCESS does not automatically differentiate between cases for letters.
However, you can use an criterion expression involving the StrComp function
to do this.

SELECT TableName1.*, TableName2.*
FROM TableName1 INNER JOIN TableName2
ON TableName1.AccountNo = TableName2.AccountNo
WHERE StrComp(TableName1.AccountNo,
TableName1.AccountNo, 0) = 0;
 
Back
Top