Another case sensitive query

  • Thread starter Thread starter Taz
  • Start date Start date
T

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.
 
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
 
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
 
Back
Top