Wildcards in SELECT statements

[Seeker]

I've got a database with a table [AddressBook] with fields like [FirstName], [LastName], [Address], [PhoneNo], etc. And I've built a form with a textbox [txtSearch], a command button that runs the code below, and a listbox [lstSearchResults] where the results should appear. There's more to it than all this but (I hope) I've included all the pertinent information. I'm trying to make it so that if a user clicks on the button while the search field is blank the listbox displays all the records in the table (for now; that's just step one). If they enter some text first, I want it to catch all the matches and display them in the listbox and I want the user to be able to enter partial names and still get matches. It seems, however, that my asterisks are not being treated as wildcards. All the recordsets return a count of zero. What do I need to do to make this work?

Dim RecSet As New ADODB.Recordset
Dim strSearchString As String

If IsNull([txtSearch]) Or [txtSearch] = "" Then
  strSearchString = "'*'"
Else
  strSearchString = "'*" & [txtSearch] & "*'"
End If

RecSet.Open "SELECT * FROM [AddressBook] WHERE [LastName] LIKE " & strSearchString & " ORDER BY [LastName];", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

RecSet.MoveFirst

Do While Not RecSet.EOF
  [lstSearchResults].AddItem RecSet("First Name") & " " & RecSet("Last Name")
  RecSet.MoveNext
Loop

Let me know if anything needs clarification.
Thanks.

 

[modest]

If [txtSearch] is the name of a field on the form, you can't call it like that. You have to actually reference the object through it's parent.

I.E.

Forms![Your Form Name Here]![txtSearch].Value
Forms("Your Form Name Here").txtSearch.Value
Me.txtSearch.Value

One of those ways should do it.

-----------------

You should also know that there may be better alternatives to this method.

 

[Seeker]

I know it would be better of me to write it in the long form but VBA is forgiving enough that you can drop the Forms![Form Name] and it still works so for this post I'm using the short version. I've tested it - that doesn't seem to be the problem, but thanks for your advice.

If you have another way you think I should do it I'm willing to take a look if you want to take the time to post it.

 

[modest]

    Dim rs              As ADODB.Recordset
    Dim cmd             As ADODB.Command
    Dim strSearchString As String

    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command

    If IsNull([txtSearch]) Or [txtSearch] = "" Then
        strSearchString = "'*'"
    Else
        strSearchString = "'*" & [txtSearch] & "*'"
    End If

'Setup the Command object
    With cmd
        .CommandText = "SELECT * FROM [AddressBook] " & _
                       "WHERE [LastName] LIKE " & strSearchString & " " & _
                       "ORDER BY [LastName];"
        .CommandType = adCmdUnknown

        'Associate the command with a connection
        .ActiveConnection = CurrentProject.Connection

        'Request the recordset
        Set rs = .Execute
    End With

    rs.MoveLast
    rs.MoveFirst

    Do While Not rs.EOF
        [lstSearchResults].AddItem rs("First Name") & " " & rs("Last Name")
        rs.MoveNext
    Loop

See if this works. If it doesn't, I would change how you call it altogether.

 

[Seeker]

Well, interesting. That didn't work either but when I check the record count it's now -1 when it always used to return 0.

If it's any consolation the way you wrote it doesn't work with the "short form" so I had to add in the Forms![Form Name] bits.

Thanks for your help though!

 

[modest]

I made a change to the code, throw in an rs.Movelast before the Movefirst and see if that helps

 

[Seeker]

Unfortunately I already tried that. Still no.

 

[Seeker]

In case anyone is still following this thread, I found it worked if I replaced the asterisks with percent signs!

Thanks for your help, Modest, and for everyone else who took a look.

 

[modest]

Asterisks should work.