Problem with upper lower case join

[Ziggy1]

Access 2000

I joined 2 identical fields in a query from 2 other tables. The data is mixed with upper/lower case letters. There is a match for each but for some reason it won't make an exact match in every case.

eg.


PFrxy PfRxy

should be

PfRxy PfRxy

These are file numbers from our main system and they mustmatch exactly.

Ziggy

 

[cpod]

In other words you want the join to be case sensitive:

AbC to AbC
But not
AbC to abC

Is this correct?

 

[Fornatian]

Use an update query on each table using the StrConv function to convert the respective field values to uppercase.

Ian

 

[Ziggy1]

yes it is case sensitive. I can't convert the text because they are unique file references that identify the record with data from another table.

I have something similar set up at work on Access 97 and I don't have a problem, so I'm not sure if it is a glitch in 2000.

These are from another query that I was able to make work. I created a second join on the product code fields and it pulled it in line. (see below)

RCky RCky
R99K R99K
RAeL RAeL
RAeL RAeL
RF0r RF0r
RAeM RAeM
RHhM RHhM
R6R3 R6R3
R6R1 R6R1
RJYe RJYe
PZEh PZEh


These ones are from a different query
I can't create a second join with this one so I'm stuck! I just want the two columns to only show the matched data.
(blank cells are expected)

RJEj RJej
RJfT
RJR6
RJhn RJhN
RJHN RJhN
RJJL RJjl
RJjy
R5xR R5xr
RJkP RJkp
RJkr
RJkZ RJkz
RJl0
RJl1
RJl2
RJL3 RJl3

Thanks

Ziggy

 

[Fornatian]

Why not create a temporary field in each table holding an uppercase version of the PK code and join on that?