findrecord finds partial input. (1 Viewer)

[John Sh]

How do I stop findrecord from returning a partial input?
My search string is "505.1" findrecord returns a record containing "505.1DM".
I cannot find a way to ensure it only returns a record with the exact search string.
Is there a way around this or do I need to use a method other than findrecord?

 

[The_Doc_Man]

How are you doing the search that finds the wrong result? Not only mechanically how, but in what context?

 

[arnelgp]

then don't use Parial search (ie: Like [txtSearch] & "*").

"[Field] = '" & txtSearch & "'"

 

[John Sh]

txtaccno is formatted as a double and is a unique number for each record. It may, or may not, have a single decimal point after the number.
The decimal point increments depending on the number of specimens collected. E.g. 505.1, 505.2 etc
txtaccnum is formatted as a string and is the individual collector's id for a particular specimen. There is no set format for this id.
strfindwhat could be "505", "505.1", "505.1DM" or "DM505.1" etc.
If the initial result for txtaccno is not equal to the search string a ".1" is added to a check string for comparison with txtaccno.
If no match the search is switched to txtaccnum. This is where the search string "505.1" returns the record with txtaccnum = '505.1DM".
The function "Messenger" replaces msgbox and "Finder" is a search across all tables for a particular number.

Public Sub gotoNewRecord(strFindWhat As String)
    Dim str    As String
    Dim frm    As Form
    If Nz(strFindWhat, "") = "" Then
        GoTo Cleanup
    Else
        Set frm = Screen.ActiveForm
        frm.Dirty = False
        frm.txtAccNo.SetFocus
        DoCmd.FindRecord strFindWhat, acStart, False, acCurrent
        If frm.txtAccNo <> strFindWhat Then
            str = strFindWhat + ".1"
            If frm.txtAccNo <> str Then
                If frm.Name = "National Parks Collection" Or frm.Name = "Herbarium Collection" Then
                    frm.txtAccNum.SetFocus
                    DoCmd.FindRecord strFindWhat, acStart, False, acCurrent
                    If StrComp(frm.txtAccNum, strFindWhat, 0) = 0 Then GoTo Cleanup
                End If
                strMessage = "Accession number " & strFindWhat & " is not currently in use " & vbCrLf & _
                            "in the '" & frm.Name & "'." & vbCrLf & _
                            "Do you want to do a ""General Search""" & "?"
                strImage = "I"
                bOK = False
                If Messenger Then
                    DoCmd.Close acForm, frm.Name
                    DoCmd.OpenForm "Finder", , , , , , strFindWhat
                    GoTo Cleanup
                End If
            End If
        End If
    End If
Cleanup:
    TempVars!trans = ""
    strFindWhat = ""
    Finder = ""
    Set frm = Nothing
End Sub

 

[John Sh]

then don't use Parial search (ie: Like [txtSearch] & "*").

"[Field] = '" & txtSearch & "'"

Not doing that, see my reply to Doc man.

 

[arnelgp]

maybe change acStart to acEntire.

DoCmd.FindRecord FindWhat:=strFindWhat, Match:=acEntire, SearchAsFormatted:=False, OnlyCurrentField:=acCurrent

 

[The_Doc_Man]

I am confused by your response. Are you saying that you call that routine more than once for the same search and that you modify the search by changing the search target by adding ".1" to something else?

The DoCmd.FindRecord has not only acStart but acEntire as optional search methods (2nd parameter). From your description of your goal, you would want to do an "exact" search the first time and a "starts with" search a second time. As your search is written, it seems that you are shooting yourself in the foot here. I.e. your code will ALWAYS find the first thing that starts with the desired string. If you wanted an exact match in preference to a "starts with" match, you should have asked for the exact match first before allowing the less exact match.

DoCmd.FindRecord method (Access)

Office VBA reference topic

learn.microsoft.com

Did my answer clarify something for you or did I misunderstand your earlier response?


EDITED by The_Doc_Man to correct a wrong option name.

 

[John Sh]

maybe change acStart to acEntire.

DoCmd.FindRecord FindWhat:=strFindWhat, Match:=acEntire, SearchAsFormatted:=False, OnlyCurrentField:=acCurrent

I think that might confuse the issue even more as my initial search is in a specific field for a specific number, then in another specific field if the fist search is unsuccessful. To search in all fields would / could return totally unrelated results

 

[arnelgp]

Match:=acEntire, means search for Exact match on same field, not on All fields.
AcFindMatch enumeration (Access) | Microsoft Learn

 

[John Sh]

Sorted, and clarified. acStart is what is needed for the first search and acEntire for the second.
I thank you both for your assistance.
John