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

Jumat, 08 Juli 2011

[MS_AccessPros] Re: Access query case-sensitivity issue

Hi Bill, Mike,

Thanks Bill. The new function shown below runs about eight
times faster then the previous Function, but it would only
make a difference with very large recordsets.

Mike, please give us feddback on what solution you use.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Function ExactStrings2(str1 As String, str2 As String) As Boolean
' Compare two string for an exact match respecting their case.
' Returns True if they are an exact match, Else False.
' Note that two null strings also returns True.
' Clive Williams 08Jul2011
ExactStrings2 = (0 = StrComp(str1, str2, vbBinaryCompare))
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Regards, Clive.

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> 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]
>