Search string with wildcards in FindFirst property

I just tested your code on one of my tables and corresponding form.  After changing "%" to "*", it worked perfectly.  What datatype is [Word]?

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...

>If I remove the signs it does work.<  What signs?

remember, when you use the InputBox, no quotes - it always returns a string.

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).

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 *.

Of course, substitute your real subform name for sfmName.

The button is in the subform.

Sorry about that.   Can you show us all the code, including the Declaration and End?

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.

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") & '%' "
 

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.

All the code is not as you posted.  Can we see the routine from start to end.

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

Line 12 is rst.FindFirst strCriteria Not FindNext!

FindNext was a typo as I mentioned.

That gives the same error as beofre.

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
     
 

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 & "'"

                                              

1:
2:

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 & "'"

Both string declarations give errors.

strSearch = 'MyWord'    'Expected Expression 

strSearch = MyWord     'Variable not defined

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"

That doesn't work, even if I enter the full exact word. 

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

Already have SP2. No missing references. I checked on another PC with Access 2003 and get the exact same results.

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

                                              

1:
2:
3:
4:
5:
6:
7:

Tried that before and got same result.

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:

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.

<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?

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?

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.

Yes - but not matching in what way? How does it fail?

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