Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Search string with wildcards in FindFirst property

Posted on 2009-06-29
39
Medium Priority
?
987 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
38 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 1332 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 668 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 1332 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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

782 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