ACCESS FEED: RE: [MS_AccessPros] Re: Access query case-sensitivity issue

Kamis, 07 Juli 2011

RE: [MS_AccessPros] Re: Access query case-sensitivity issue

Clive

What about a function that uses StrComp() with the binary option? You'd first
have to make a text compare and then if they match use a binary to make sure
they match in case as well as text.

And you're right SQL 2005 will let you set up a DB as case-sensitive and a
stored procedure would have to be used for the comparison.

Regards,

Bill Mosca,

Founder, MS_Access_Professionals

That'll do IT <http://www.thatlldoit.com/> http://thatlldoit.com

MS Access MVP

<http://mvp.support.microsoft.com/profile/Bill.Mosca>
http://mvp.support.microsoft.com/profile/Bill.Mosca

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of horastacatto
Sent: Thursday, July 07, 2011 11:46 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Access query case-sensitivity issue

Hi Mike,

As far as I know Access does not have a respect case operation
but I would expect SQL 2005 to have one.

You could use the following Function in your Query:

Public Function ExactStrings(Str1 As String, str2 As String) As Boolean
' Compare two string for an exact match respecting their case.
' Clive Williams 07Jul2011
Dim i As Integer
If Len(Str1) <> Len(str2) Then Exit Function
' Comment out the next line if you want True for two zero-length strings.
If Len(Str1) = 0 Then Exit Function
For i = 1 To Len(Str1)
If Asc(Mid(Str1, i, 1)) <> Asc(Mid(str2, i, 1)) Then Exit Function
Next i
ExactStrings = True
End Function

Hope that helps.

Regards, Clive.

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "mikewolfstone"
<mikewolfstone@...> wrote:
>
> I have an access query updating a table with a value from a table in SQL 2005.
Some entries in the SQL reference table have mixed case. For example 9P is
something different than 9p. When I run the query, it tends to match up to the
item with the latter collating sequence item (9P) and that is the wrong answer.
Is there a way to have the query operate case-sensitive? How would I do that?
> Thanks.
>

[Non-text portions of this message have been removed]