Query in "criteria" in VBA code

[unknown2u]

I stormed google searching for a solution for this and found nothing.

I want to use the .findfirst\next\last method using a criteria that uses a string variable AND a "begins with" wildcard.
I don't understand how to write it at all.

Basically it should say something like this:

In recordset "rs", in the field "test" find the first record that begins with whatever the string variable "searchString" holds.
I tried copying and even converting SQL codes but it just doesn't work...

Please help.
Thanks in advance

 

[arnelgp]

this can be done in query:

select * from yourTable where [test] like '" & strSearch & "*';"

 

[unknown2u]

I tried writing rs.findlast(*insert the line you wrote*)
and i got a syntax error.
That's exactly the problem I don't know what the syntax should be..

Any ideas?

 

[Pyro]

To expand on arnelgp's post:

dim db as DAO.Database
dim rs as DAO.Recordset
dim strSQL as String
dim strSearch as String

set db = Currentdb()

strSearch = "Some criteria"
strSQL = "SELECT * FROM yourTable WHERE [test] like '" & strSearch & "*';"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
     If .RecordCount <> 0 Then
          'Do some stuff with the records that match your criteria

     End If

End With

rs.Close
Set rs = Nothing
Set db = Nothing

 

[arnelgp]

what other code do you have so far? i dont think you can use wildcards (ie Like '*text*') in rs method .findfirs/next/last.

 

[unknown2u]

AWESOME! Thank you so much you!
I took what you guys wrote and changed it simply:
rs.findlast "[test] LIKE '" & searchString &"*'"
and it worked perfectly!

So i just skipped the SELECT and FROM part.

I never get all the quote marks and when to use one or two and where...

Thanks again!

 

[Pyro]

Just beware of the possibility for your search string to already contain quote marks. Example: searching for a surname like O'Neil. This will throw an error and not return a result.

I always pass my criteria through a function that escapes quotes:

'Escape quotes
Public Function Escape_Quotes(strData As String)
On Error GoTo Err_Escape_Quotes

Dim strNew As String
    
strNew = Replace(strData, "'", "''")
strNew = Replace(strNew, "", """")

Escape_Quotes = strNew

Exit_Escape_Quotes:
    Exit Function

Err_Escape_Quotes:
    MsgBox Err.Description
    Resume Exit_Escape_Quotes
    
End Function

 

[arnelgp]

i dont think you can do search on rs.findfirst/last/next using wildcard ("Like '*something*'").
you should test your rs (rs.NoMatch), if indeed it find the match. if it returns true then it did not find anything. using Like '*' will succeed your code, no error but will not find what you are searching.

 

[unknown2u]

Thanks for the tip Pyro. I'll make note of that.

arnelgp I didn't get what you mean by that... even you wrote that kind of expression and it even says so on Microsoft's Development Center https://msdn.microsoft.com/en-us/library/office/ff195128.aspx