Case sensitive joins

[mbhCFO]

I have a table with a field containing both upper and lower case letters that
I am attempting to create a join with another table. However, because it
isn't case-sensitive it does not return the correct data. Is there something
I'm missing here or is there a work around?

 

[Sylvain Lafontaine]

See http://support.microsoft.com/kb/244693 .

 

[Lance]

easiest way is to use the strcomp function. Something like:

SELECT Table1.ID, Table1.Description
FROM Table1 INNER JOIN Table2 ON StrComp(Table1.ID, Table2.ID, 0) = 0

 

[Jerry Whittle]

Access isn't case sensitive. If this was Oracle, you'd have the opposite
problem where Jerry does not equal JERRY in queries.

If, big IF, the first character of the string makes all the difference, you
could use the ASC function to see if the first character is the same in both
fields. Something like below will exclude a joing between jerry and Jerry.
However it wouldn't catch Jerry and JERRY as the ASC function only evaluates
the first character in the string.

SELECT Admin.Name, AdminCase.Name
FROM Admin INNER JOIN AdminCase ON Admin.Name = AdminCase.Name
WHERE Asc([Admin].[Name])=Asc([AdminCase].[Name]) ;

 

[John W. Vinson]

I have a table with a field containing both upper and lower case letters that
I am attempting to create a join with another table. However, because it
isn't case-sensitive it does not return the correct data. Is there something
I'm missing here or is there a work around?

What version of Access? JET (.mdb or .accdb) database, or are the tables
linked from some other software such as SQL/Server?

It's rather difficult to force JET to be case sensitive - in fact I don't know
of a way to do so other than to put the data into SQL or MySQL or another
backend which supports case-sensitive joins, or adding an auxiliary field
containing the hexadecimal expansion of the field's value.

John W. Vinson [MVP]

 

[Jerry Whittle]

Sylvain Lafontaine's and Lance's replies are much more helpful than mine.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Access isn't case sensitive. If this was Oracle, you'd have the opposite
problem where Jerry does not equal JERRY in queries.

If, big IF, the first character of the string makes all the difference, you
could use the ASC function to see if the first character is the same in both
fields. Something like below will exclude a joing between jerry and Jerry.
However it wouldn't catch Jerry and JERRY as the ASC function only evaluates
the first character in the string.

SELECT Admin.Name, AdminCase.Name
FROM Admin INNER JOIN AdminCase ON Admin.Name = AdminCase.Name
WHERE Asc([Admin].[Name])=Asc([AdminCase].[Name]) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table with a field containing both upper and lower case letters that
I am attempting to create a join with another table. However, because it
isn't case-sensitive it does not return the correct data. Is there something
I'm missing here or is there a work around?