Solved

Search string with wildcards in FindFirst property

Posted on 2009-06-29
39
865 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:Papote
  • 16
  • 7
  • 7
  • +5
39 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24737433
DAO uses * not %
0
 
LVL 44

Expert Comment

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

Author Comment

by:Papote
ID: 24738104
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24738456
>If I remove the signs it does work.<  What signs?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24738482
remember, when you use the InputBox, no quotes - it always returns a string.
0
 

Author Comment

by:Papote
ID: 24739289
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
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 333 total points
ID: 24739812
try this:
          strCriteria = "[Word] Like '%' & strSearch & '%' "

        I believe T-SQL uses a single quote delimiter.
0
 

Author Comment

by:Papote
ID: 24746327
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 167 total points
ID: 24746536
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24746546
Of course, substitute your real subform name for sfmName.
0
 

Author Comment

by:Papote
ID: 24747437
The button is in the subform.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24749467
Sorry about that.   Can you show us all the code, including the Declaration and End?
0
 

Author Comment

by:Papote
ID: 24753716
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24753938
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
 

Author Comment

by:Papote
ID: 24756363
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24756468
All the code is not as you posted.  Can we see the routine from start to end.
0
 

Author Comment

by:Papote
ID: 24756977
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
 

Author Comment

by:Papote
ID: 24756990
Line 12 is rst.FindFirst strCriteria Not FindNext!
0
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 333 total points
ID: 24757099
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Papote
ID: 24771934
FindNext was a typo as I mentioned.
That gives the same error as beofre.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24772380
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
 

Author Comment

by:Papote
ID: 24772826
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24772999
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
 

Author Comment

by:Papote
ID: 24807619
Both string declarations give errors.
strSearch = 'MyWord'    'Expected Expression 
strSearch = MyWord     'Variable not defined
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24810921
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
 

Author Comment

by:Papote
ID: 24813331
That doesn't work, even if I enter the full exact word. 
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24813491
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
 

Author Comment

by:Papote
ID: 24813914
Already have SP2. No missing references. I checked on another PC with Access 2003 and get the exact same results.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24849668
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
 

Author Comment

by:Papote
ID: 24853072
Tried that before and got same result.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24853538
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
 

Author Comment

by:Papote
ID: 24859849
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
 
LVL 84
ID: 24906137
<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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24906851
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
 

Author Comment

by:Papote
ID: 24907234
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24907890
Yes - but not matching in what way? How does it fail?
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 24908521
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
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 24909736
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now