Solved findfirst with a variable for the field name and "Like" and a variable in the criteria.

[John Sh]

I know this is an old dog but it still has a bite
In the code below, sStr can be a string or a "number"
If it is a number then findfirst returns not rs.nomatch
If sStr is a string that is exactly the same as the field value then success.
If sStr is not exactly the same as the field value then the result is always "rs.nomatch".
I.e sStr = "Genus". Field = "Genus mis-match".
I would like it to return all fields which include "Genus"
I have tried every combination of string concatenation I can think of, and some that AI came up with, to no avail.

rs.findfirst "[" & sField & "] like '*" & sStr & "*'"

I'm sure the solution is simple, but as of now, it is beyond me.

Public Sub Find_Disc(frm As Form, sStr As String, sField as string)
    Dim rs    As Recordset
    Set rs = frm.RecordsetClone
    If Val(sStr) > 0 Then
        rs.FindFirst sField & "= " & sStr     'This works with a numeric input
    Else
        rs.FindFirst sField & "= '" & sStr & "'"    'This works if the input is the same as the field value.
        sStr = "*" & sStr & "*"
        If rs.NoMatch Then rs.FindFirst "sField Like 'sStr'"   'This does not work.
    End If
    if not rs.nomatch then
        frm.Bookmark = rs.Bookmark
        FocusIt frm, sField
    end if
Cleanup:
    Set rs = Nothing
    Set frm = Nothing
end sub

As always, any help is greatly appreciated.
John

 

[cheekybuddha]

rs.findfirst "[" & sField & "] like '*" & sStr & "*'"

This should work.

Using a variable for your criteria will make it easier to debug what is going wrong

Dim sCriteria As String
sCriteria =  "[" & sField & "] like '*" & sStr & "*'"
Debug.Print sCriteria
rs.findfirst sCriteria

One thing you may try if it doesn't work is:

Dim sCriteria As String
sCriteria =  "[" & sField & "] like '%" & sStr & "%'"
Debug.Print sCriteria
rs.findfirst sCriteria

If sStr might contain a single quote then also try:

Dim sCriteria As String
sCriteria =  "[" & sField & "] like '*" & Replace(sStr, "'", "''") & "*'"
Debug.Print sCriteria
rs.findfirst sCriteria

Each time you try a variation you can inspect exactly what criteria you have built in the Immediate Window (Ctrl+G).

Paste here what doesn't work

 

[arnelgp]

or something lik this:

    If Val(sStr) > 0 Then
        rs.FindFirst sField & "= " & sStr     'This works with a numeric input
    Else
        rs.FindFirst sField & "= '" & sStr & "'"    'This works if the input is the same as the field value.
        sStr = "'*" & sStr & "*'"
        If rs.NoMatch Then rs.FindFirst sField & " Like " & sStr
    End If

 

[John Sh]

sCriteria = "[" & sField & "] like '%" & sStr & "%'"

Replacing * with % did the trick.
ArnelGP your solution did not work.
I finished up with

rs.findfirst sField & " like '%" & sStr & "%'"

Thank you both.
John

 

[cheekybuddha]

The reason you are needing % instead of * is either because you (or someone) has changed the database settings to use ANSI SQL (unnecessary in most instances), or your rs recordset variable is a pass-through query to a RDBMS.

My guess is that it is the former reason; and, unless it is set for a specific requirement, I would suggest unchecking it.

 

[GPGeorge]

Check here for the setting David called out:

 

[isladogs]

You will probably find Access changed Like to Alike when you substituted with "%".
When you untick the ANSI-92 setting revert to Like and "*"

 

[cheekybuddha]

You will probably find Access changed Like to Alike when you substituted with "%".

Only in the query designer for a stored query, not in a VBA string.

 

[isladogs]

True. Of course strictly speaking I should have written ALike with a capital L (not Alike)

 

[John Sh]

Understood.
I changed that setting some years ago, for what, at the time, seemed a good reason.
To change it back, now, involves changing many, many, queries so I'm going to leave it as is for now.
I thank you all for the input.
John