Another case sensitive query

[Taz]

I'm attempting to link a field in one table to a field in another table and
it is only matching where the case of the letters match. All the fields in
one table are uppercase and only a few in the other table are uppercase -
those are the only ones that will link.

 

[vanderghast]

If you look for an inner join like temporary link, then see if you cannot
use the WHERE clause and StrComp VBA function (which returns 0 in case of
equality of the two strings, accordingly the third argument of StrComp).

If you look for an enforced data relationship, I am afraid you can't, since
Jet is basically case insensitive.


Vanderghast, Access MVP

 

[John Spencer MVP]

Where are these tables stored? They are not in a JET database (native Access
database) or they would not be case sensitive.

If you want can force the values to all upper case in the join clause

SELECT *
FROM TableAllUpper INNER JOIN TableMixed
ON TableAllUpper.SomeField = UCase(TableMixed.SomeMixedField)

Alternative would be to create a query on the mixed case table and create a
new calculated column to match on.
Field: SomeFieldUpper: UCase(Somefield)

Now select the query and the other table as sources and build your query

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County