How to Make Link Distinguishing Between Lower and Upper Cases.

[doyle60]

I need to bring two tables from a Main Frame computer into my computer
that are linked with key fields that distinguish between upper and
lower cases.

For example, the Code field (Key field) in one table contains:

A
B
C ...
1
2
3...
a
b
c...

And "A" is not the same as "a".

How can I get Access to make the link? I thought I would change the
lower case letters to A1, B2, etc. in both tables. But I don't know
how to identify if lower case. Is there a function that returns "Yes"
or "No" or anything you want if lower case?

Or how should I go about this?

Thanks,

Matt

 

[Guest]

You could use the cbool command

dim myval as boolean

myval = cbool(lcase(string) = string)

if the value represented by string is lowercase then myval will return a
value of true, if the value represented by string is uppercase then myval
will return a vlue of false.

 

[doyle60]

Is this a function I should create? I'm not sure how to implement
this. Could you explain? Thanks,

Matt

 

[John Spencer]

VBA function StrComp

The following will return True if the two strings are a match and -1, 1, or
null otherwise
StrComp("AA","aa",0) = 0

Or you can use ASC fto compare to single letters
Asc("A") = Asc("a")

Access is not normally case-sensitive if you are using an mdb. If you are
linking to other data sources, there are cases when Access can be
case-sensitive.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[doyle60]

I tried to use your function but get an error. I don't understand
functions that well.

I created a new function StrCompFunction and put the following in:

Function StrComp()

StrComp("AA", "aa", 0) = 0

End Function

I than created a query drawing up the two tables. I made the link
between the two case sensitve fields, and it produces the expected
duplicating results, linking:

"A" with "a"
"A" with "A"
"a" with "A"
"a" with "a"
"1" with "1"
"2" with "2"

Now I tried to use your function to delete the unwanted matches. I
created a field called Match:

Match: StrComp([SCA_CODEAdjTAMF21],[SCA_CODEAdjSTMF221],0)

But I get a wrong number of arguments error.

(If it worked I was going to then use the Criteria row to return only
desired records.)

Thanks,

Matt

 

[doyle60]

I tried to use your function but get an error. I don't understand
functions that well.

I created a new function StrCompFunction and put the following in:

Function StrComp()

StrComp("AA", "aa", 0) = 0

End Function

I than created a query drawing up the two tables. I made the link
between the two case sensitve fields, and it produces the expected
duplicating results, linking:

"A" with "a"
"A" with "A"
"a" with "A"
"a" with "a"
"1" with "1"
"2" with "2"

Now I tried to use your function to delete the unwanted matches. I
created a field called Match:

Match: StrComp([SCA_CODEAdjTAMF21],[SCA_CODEAdjSTMF221],0)

But I get a wrong number of arguments error.

(If it worked I was going to then use the Criteria row to return only
desired records.)

Thanks,

Matt

 

[John Spencer]

StrComp is an existing VBA function in Access 2000 (and later). SO you
should not create a function with that name at all. Try DELETING the
function from your VBA module and then try the query again.

In SQL, I would expect to see something like the following.

SELECT TableA.*, TableB.*
,StrComp([SCA_CODEAdjTAMF21],[SCA_CODEAdjSTMF221]) as Match
FROM TableA INNER JOIN TableB
ON tableA.SCA_CODEAdjTAMF21 = TableB.SCA_CODEAdjSTMF221
WHERE StrComp([SCA_CODEAdjTAMF21],[SCA_CODEAdjSTMF221]) = 0



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[doyle60]

I tried to use your function but get an error. I don't understand
functions that well.

I created a new function StrCompFunction and put the following in:

Function StrComp()

StrComp("AA", "aa", 0) = 0

End Function

I than created a query drawing up the two tables. I made the link
between the two case sensitve fields, and it produces the expected
duplicating results, linking:

"A" with "a"
"A" with "A"
"a" with "A"
"a" with "a"
"1" with "1"
"2" with "2"

Now I tried to use your function to delete the unwanted matches. I
created a field called Match:

Match: StrComp([SCA_CODEAdjTAMF21],[SCA_CODEAdjSTMF221],0)

But I get a wrong number of arguments error.

(If it worked I was going to then use the Criteria row to return only
desired records.)

Thanks,

Matt

 

[doyle60]

Thanks. Deleted function. Didn't work. I twiddled around with it.
I experimented. I reviewed earlier posts. Found problem. Your
expected SQL was missing a minor detail, hinted at earlier.

Wrong: StrComp([Field1],[Field2]) as Match
Right : StrComp([Field1],[Field2],0) as Match

Thanks so much,

Matt