Query Join is case sensative

[zoldy]

I am doing a join in access and it is not matching these two fields....

9p23-BA4B
9P23-BA4B

If I change the p to uppercase it works...

can I change the join to not be case sensative?

thanks in advance

 

[SgtJarrow]

What version of Access are you using? Can you post the SQL for your join?

I created a quick test in AccessXP (2000 format) and they matched, even with on being lowercase...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[zoldy]

the table I am linking to is a DB2 ... I have had similar issues with these....

Alternately can I force Upper case on import in to my user table?

The data on one side is db2 the data on the other side is imported...

 

[SgtJarrow]

Depending on how you import yes....or even after import run an update query against your imported data and change all imported data to uppercase...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[dhookom]

Some linked tables may be case-sensitive such as DB2 and some DBF, etc. If you don't want to either import into Access tables or update the data in the native tables, you can omit the join and use criteria so your sql would look like:
WHERE UCase(tblA.FieldA) = UCase(tblB.FieldB)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.