Search string with wildcards in FindFirst property

I created a custom search on a subform where the textboxes are not enabled and locked, so it will select the record. I am having a hard time using LIKE with wildcards. If I type the full name instead of the variable it works. I want it to find what I type in whichever area of the field.

I have changed the percentage sign with asterisk and it doesn't work.

Without the wildcard, if I enter the full correct word and it does work. The problem lies in the wildcard.

I have even tried using ALike and get the same result.

I am on Access 2007, but the file format is 2000 with the back end running on SQL Server 2005.
Dim strCriteria As String
Dim rst As DAO.Recordset
Dim strSearch As String
 
strSearch = InputBox("Search")
 
Set rst = Me.RecordsetClone
strCriteria = "[Word] Like '%" & strSearch & "%'"
rst.FindFirst strCriteria
 
Me.Bookmark = rst.Bookmark

Open in new window

PapoteAsked:
Who is Participating?
 
puppydogbuddyConnect With a Mentor Commented:
try this:
          strCriteria = "[Word] Like '%' & strSearch & '%' "

        I believe T-SQL uses a single quote delimiter.
0
 
peter57rCommented:
DAO uses * not %
0
 
GRayLCommented:
I just tested your code on one of my tables and corresponding form.  After changing "%" to "*", it worked perfectly.  What datatype is [Word]?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
PapoteAuthor Commented:
The field [Word] is nvarchar.
If I set the variable to the text I am looking for: strSearch = "myWord", it still doesn't work. Changing the % to *. If I remove the signs it does work.
This is just wierd...
0
 
GRayLCommented:
>If I remove the signs it does work.<  What signs?
0
 
GRayLCommented:
remember, when you use the InputBox, no quotes - it always returns a string.
0
 
PapoteAuthor Commented:
If I don't use any wildcards and type the EXACT word it goes to the record. By signs I mean the The percent sign or asterisk. I get the same results if I use the input box or just enter the string directly (assigning the variable to the string).
0
 
PapoteAuthor Commented:
With that last statement I get the following error: "The Microsoft Office Access database engine does not recognize 'strSearch' as a valid field name or expression."
Even If I changed % to *.

0
 
GRayLConnect With a Mentor Commented:
Key word - subform.  Change line 7 to:

Set rst = Me.Form!sfmName.RecordsetClone

have a read of http://www.mvps.org/access/forms/frm0031.htm
0
 
GRayLCommented:
Of course, substitute your real subform name for sfmName.
0
 
PapoteAuthor Commented:
The button is in the subform.
0
 
GRayLCommented:
Sorry about that.   Can you show us all the code, including the Declaration and End?
0
 
PapoteAuthor Commented:
The code is on my first post. All I did is add a "search" button on the subform footer. Like I've mentioned before, the code works just the wildcards that do not.
0
 
puppydogbuddyCommented:
Papote
the variable strSearch may be misinterpreted because of the embedded input box function. Try it with the InputBox explicitly declared as shown below.

try this:
          strCriteria = "[Word] Like '%' & InputBox("Search") & '%' "
 
0
 
PapoteAuthor Commented:
puppydogbuddy: That gives a syntax error. I Have put the full correct word in the input box as well as subsituting the variable and without the wild cards it works.
0
 
GRayLCommented:
All the code is not as you posted.  Can we see the routine from start to end.
0
 
PapoteAuthor Commented:
Tweaked it a bit. This code works as long as I enter the EXACT string in the inputbox.
Private Sub btnSearch_Click()
On Error GoTo Err_btnSearch_Click
 
Dim strCriteria As String
Dim rst As DAO.Recordset
Dim strSearch As String
 
strSearch = InputBox("Search")
 
Set rst = Me.RecordsetClone
strCriteria = "[Word] Like '" & strSearch & "'"
rst.FindNext strCriteria
 
If Not rst.NoMatch Then
    If Not rst.EOF Then Me.Bookmark = rst.Bookmark
Else    ' If no Match
    MsgBox "No match"
End If
 
rst.Close
 
Exit_btnSearch_Click:
    Set rst = Nothing
    Exit Sub
 
Err_btnSearch_Click:
    MsgBox Err.Description
    Resume Exit_btnSearch_Click
End Sub

Open in new window

0
 
PapoteAuthor Commented:
Line 12 is rst.FindFirst strCriteria Not FindNext!
0
 
puppydogbuddyConnect With a Mentor Commented:
was the FindNext the problem, or is that just a typo on the code you posted?

If you still have a problem,  I think it may be because I forgot to use the single quote delimiter on the contents of the input box in strSearch.  This change is in addition to one I gave you for strCriteria below.

strSearch = InputBox('Search')
strCriteria = "[Word] Like '%' & strSearch & '%' "

the reason I am asking you to try the single quote delimiter around parameters is because the use of the single quote delimiter is accepted by both Access Jet and T-SQL, whereas the double quote delimiter is accepted by  
0
 
PapoteAuthor Commented:
FindNext was a typo as I mentioned.
That gives the same error as beofre.
0
 
puppydogbuddyCommented:
OK, I think I figured out your problem. You are not using the correct vba syntax for the InputBox function, which requires you to directly enter the values for each argument or assign the values to declared variables in a specific order.  This is the line you need to fix:
                        strSearch = InputBox("Search")
 
see this link:
               http://office.microsoft.com/en-us/access/HA012288561033.aspx
     
 
0
 
PapoteAuthor Commented:
Uhm isn't that exactly what I have?
Anyways like I mentioned before, I have removed the inpur box and entered the search string in the variable string declaration and get the exact same results. So, I still think it still has to do with the wildcards.


strSearch = "MyWord"
strCriteria = "[Word] Like '" & strSearch & "'"

Open in new window

0
 
puppydogbuddyCommented:
Your example w/o an input box is using a double quote delimiter.  Try it this way:

strSearch = 'MyWord'
strCriteria = "[Word] Like '" & strSearch & "'"

if the above does not work, remove the quotes entirely from strSearch.  Your delimeters in  strCriteria are probably sufficient.

strSearch = MyWord
strCriteria = "[Word] Like '" & strSearch & "'"
0
 
PapoteAuthor Commented:
Both string declarations give errors.
strSearch = 'MyWord'    'Expected Expression 
strSearch = MyWord     'Variable not defined
0
 
puppydogbuddyCommented:
Papote,
Try this. If this does not work, I give up.

strSearch = InputBox("Search")

strCriteria = "[Word] Like '*" & strSearch & "*'"              'note single quote on each asterisk
_______________________________________
The above should produce a clause like this:
     [Word] Like '*MyWord*'
assuming that the Input Box string contains the word "MyWord"


0
 
PapoteAuthor Commented:
That doesn't work, even if I enter the full exact word. 
0
 
puppydogbuddyCommented:
Check for missing vb library references.  If references are ok, then it would appear to be a glitch in Access 2007. If you have not done it already, you should upgrade Office 2007 through SP2. There are still plenty of bugs in 2007.  See this link :
                           http://allenbrowne.com/Access2007.html
0
 
PapoteAuthor Commented:
Already have SP2. No missing references. I checked on another PC with Access 2003 and get the exact same results.
0
 
Jim P.Commented:
For testing always comment out the error handling.

As for DAO -- I think they pretty much have given up development since Access 2000. Or does Acc07 have something better than DAO 3.6 for a reference?

As for the issue -- Try adding a MoveFirst in like below and see what happens.
'On Error GoTo Err_btnSearch_Click
 
Set rst = Me.RecordsetClone
rst.MoveFirst
strCriteria = "[Word] Like '*" & strSearch & "*'"
'Debug.Print strCriteria
rst.FindNext strCriteria

Open in new window

0
 
PapoteAuthor Commented:
Tried that before and got same result.
0
 
Jim P.Commented:
I'm using the code below without problems against a copy of the Address table out of the AdventureWorks db. The AddressLine1 is an NVarchar(60).

At this point -- things to check:

Can you do a full compile of the DB without errors? VBA Window --> Debug --> Compile MyDB.

Is the table updateable in the datasheet view?

Does the table have a PK on the SQL Side?

How big is the NVarchar field?

Have you tried it against a different field in the same table?
Private Sub btnSearch_Click()
'On Error GoTo Err_btnSearch_Click
 
 
Dim strCriteria As String
Dim rst As DAO.Recordset
Dim strSearch As String
 
strSearch = InputBox("Search")
 
Set rst = Me.RecordsetClone
rst.MoveFirst
strCriteria = "[AddressLine1] Like '*" & strSearch & "*'"
Debug.Print strCriteria
rst.FindNext strCriteria
 
If Not rst.NoMatch Then
    If Not rst.EOF Then Me.Bookmark = rst.Bookmark
Else    ' If no Match
    MsgBox "No match"
End If
 
rst.Close
 
Exit Sub
Exit_btnSearch_Click:
    Set rst = Nothing
    Exit Sub
 
Err_btnSearch_Click:
    MsgBox Err.Description
    Resume Exit_btnSearch_Click
 
End Sub

Open in new window

0
 
PapoteAuthor Commented:
I usually compile with no problems.
The subform is derived from a query (recordset). The Text box I am searching is a combobox with a recordsource set to translate the ID Number to the string (nvarchar(50)).
All my tables have Primary Keys.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<The subform is derived from a query (recordset). >

Do you mean you're using a Query as a Subform? Or that you're using a Form as a Subform, and that form is based on a Query?
0
 
Leigh PurvisDatabase DeveloperCommented:
And has it actually been stated anywhere what the problem is?
Beyond the old "doesn't work" I mean...
The error - or the exact way in which it's failing?
0
 
PapoteAuthor Commented:
LSMConsulting:
I mean it is a form as a Subform, and that form is based on a query..

LPurvis:
Well it boils down to .FindFirst and .FindNext not matching when utilizing wildcard characters in the criterea. If the criterea is exact AND there are NO wildcard characters then there is a match.
0
 
Leigh PurvisDatabase DeveloperCommented:
Yes - but not matching in what way? How does it fail?
0
 
GrahamMandenoCommented:
Papote,

In 24771934 you said:
> That gives the same error as beofre.

What *is* the error?  As far as I can tell, all you have said is "It doesn't work".
--
Graham Mandeno - Access MVP
0
 
GrahamMandenoCommented:
I just noticed that in 24859849 you said:
> The Text box I am searching is a combobox with a recordsource set to translate the ID Number to the string (nvarchar(50)).
Are you saying that the field [Word] is actually a numeric foreign key related to an ID number in another table, and that the corresponding text resides in that related table?
If so, then you are searching a numeric field for a text string and of course it won't work!
There are a lot of us playing guessing games here.  Maybe you could post a loiilt more information about your table structure and relationships, including the RecordSource of your subform and the RowSource, ControlSource and BoundColumn of your combo box.
--
Graham Mandeno [Access MVP]
0
All Courses

From novice to tech pro — start learning today.