Case sensitive joins

M

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?
 
L

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
 
J

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]) ;
 
J

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]
 
J

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.

Jerry Whittle said:
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.


mbhCFO said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top