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?

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

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"mbhCFO" <(E-Mail Removed)> wrote in message
news:7ED0328A-BDBA-4119-82A8-(E-Mail Removed)...
>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?

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


"mbhCFO" wrote:

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

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" wrote:

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

On Tue, 6 Nov 2007 08:59:04 -0800, mbhCFO <(E-Mail Removed)>
wrote:

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

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" wrote:

> 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" wrote:
>
> > 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?