How to Make Link Distinguishing Between Lower and Upper Cases.

  • Thread starter Thread starter doyle60
  • Start date Start date
D

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
 
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.
 
Is this a function I should create? I'm not sure how to implement
this. Could you explain? Thanks,

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