rst.FindFirst - Case Sensitive for String field

Cosmos75 · Dec 5, 2003

I've seen some threads on finding a record but did not see one asking how to look for a string field and have it be case sensitive.

I have this code

PHP:

Dim myDb As DAO.Database
Dim rst As DAO.Recordset
Dim pwFind As String

pwFind = "abc"

Set myDb = CurrentDb()
Set rst = myDb.openrecordset("tblPassword", dbOpenDynaset)

rst.FindFirst ("[PassWord] = " & Chr(39) & pwFind & Chr(39))

If rst.NoMatch Then
MsgBox "No Match"
Else
MsgBox "Found a Match"
End If

e.g. if I am looking abc and only ABC exists, it should returm no match found instead the way I have it now it isn't case-sensitive.

Travis · Dec 5, 2003

Option Compare Statement

Used at module level to declare the default comparison method to use when string data is compared.

Syntax

Option Compare {Binary | Text | Database}

Remarks

If used, the Option Compare statement must appear in a module before any procedures.

The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a module. If a module doesn't include an Option Compare statement, the default text comparison method is Binary.

Option Compare Binary results in string comparisons based on a sort order derived from the internal binary representations of the characters. In Microsoft Windows, sort order is determined by the code page. A typical binary sort order is shown in the following example:

A < B < E < Z < a < b < e < z < Ã€ < ÃŠ < Ã˜ < Ã_ < Ãª < Ã¸

Option Compare Text results in string comparisons based on a case-insensitive text sort order determined by your system's locale. When the same characters are sorted using Option Compare Text, the following text sort order is produced:

(A=a) < ( Ã€=Ã_) < (B=b) < (E=e) < (ÃŠ=Ãª) < (Z=z) < (Ã˜=Ã¸)

Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.

Basically for the module in which you are doing the Find use "Binary" that way "a"<>"A". Just remember that this will be true for all procedures in the module.

Cosmos75 · Dec 5, 2003

Travis,

I did see that (Option Compare Binary) in the book VB & VBA in a Nutshell by Paul Lomax. But was afriad to use it as I figured out that it would be true for all my procedures in the form and wasn't sure what affect it would have on the rest of my code.

Cosmos75 · Dec 5, 2003

Travis,

Just tried adding Option Compare Binary.

It still tells me that a match was found for pwFind = "abc" even though the only three character password I have is "ABC"

Cosmos75 · Dec 5, 2003

rst.FindNext (DAO)

With help from JustListenen at http://www.accessvba.com, here is the code that I ended up with;

PHP:

Dim myDb   As DAO.Database
Dim rst    As DAO.Recordset
Dim pwFind As String

pwFind = "abc"

Set myDb = CurrentDb()
Set rst = myDb.openrecordset("tblPassword", dbOpenDynaset)

Do While Not rst.EOF And pwFound <> pwFind

    rst.FindNext ("[PassWord] = " & Chr(39) & pwFind & Chr(39))
    
    If rst.NoMatch Then
        MsgBox "No Match"
        GoTo ExitNow:
    Else
        pwFound = rst.Fields("PassWord")
        MsgBox "Found a Match : " & pwFound
        If pwFound = pwFind Then
            MsgBox "Case Match Found"
            GoTo ExitNow:
        Else
            MsgBox "Case Match Not Found"
        End If
    End If
    
Loop

ExitNow:
    Set rst = Nothing
    Set myDb = Nothing

To see that thread: http://www.accessvba.com/showthread.php?s=&threadid=7066

rst.FindFirst - Case Sensitive for String field

Cosmos75

Registered User.

Travis

Registered User.

Cosmos75

Registered User.

Cosmos75

Registered User.

Cosmos75

Registered User.

Similar threads

Users who are viewing this thread