Exact match using case-sensitive 1

[tamtf]

I have a table with ID's, some of which appear multiple times. Each instance of these ID's is spelled using a different combination of upper and lowercase. Access disregards each instance's case and thinks these ID's are duplicates. I need to create a relationship between two tables using the ID field as a unique field. Is there a VBA code available to do this?
Thanks for your help

 

[cheerio]

The jet engine that underlies Access is not case sensitive.

This means that you cannot directly join fields and get meaningful results as the engine is incapable of seeing the case differences.

It is possible to create a query that does a case sensitive join but the process is a bit home brew and would therefore be possibly inefficient.

Add to the design grid a calulated field:

strcomp(table1.ID,table2.ID,0) where the first two items are the fields you are trying to compare.

Set a criteria for the calculated field of 0 (a zero).

strcomp is case sensitive when the third parameter is a zero. It returns a zero answer if there is an exact match.

Ken

 

[allanon]

This is very similar to a question asked a couple of weeks ago. There is a way but it requires a little work. You have to create a new field in your tables to key on. In this table you store the ascii value of your key fields and then link on these ascii values. If you need more help let me know.

 

[PaulF]

You can use strComp() in order to accomplish Case Sensitive Matches.

strComp(FirstValue, SecondValue,0) = 0
the 0 tells the Function to perform a Binary Compare
= 0 tells the Function that you want an exact match

The following example uses it and matches Field1 in tbl1 to Field1 in tbl2. In order to create the Query, use the QBE grid and get all of the fields you want listed, join the two tables on the key field, then switch to SQL view and change the Join statement to use the strComp Function

SELECT tbl1.Field1, tbl2.Field1, tbl1.Field3, tbl2.Field2
FROM tbl1 INNER JOIN tbl2 ON strComp(tbl1.Field1, tbl2.Field1,0)=0;


PaulF