How to insert a wildcard parameter into VBA code

[khurley]

Hi Everyone. In my Access Database I have a subform on a Main form. I am trying to do a lookup function on the subform. I have had to created an unbound combo box which lists the three items i can seach on (address, city or state) and a text box where I type in my search criteria (ex: 4089 Main Street) and a search toggle button which goes and searches the data and returns the hits in the form.

I have this working perfecttly as long as I type in an exact match in my text search box. I am trying to add in the asterisk parameter so that the returned results will include all results that have the search criteria in them.

for example: If i type in 4089 Main my search results would return:

4089 Main St.
4089 Main Street
4089 Main Avenue
124089 Main Street

etc....

Here is my line of code for the search string:

strSQL = "SELECT DISTINCT Locations.ID FROM Locations WHERE ID Is Not Null and (" & Me!cboSelectField & "='" & Me!txtSearchText & "'" & &quot"

I have tried putting the Asterisks everywhere but I am doing somethign wrong. I keep getting syntax errors... or invalid operator errors. I think i must be missing the boat here with some punctuation.

The code referenced above works fine with exact matching.

Any help would be VERY muh appreciated!

Thanks
Kathy

 

[khurley]

Here's the rest of the code for the toggle button.. thought it might make more sense..... Thanks.... FRM Dim'd as From outside this code.

Private Sub btnSearch_Click()
Set FRM = Me.Parent
    If Me.btnSearch.Value = -1 Then
        Me.btnSearch.Caption = "Remove Filter"
        Dim CN As ADODB.Connection
        Dim RS As ADODB.Recordset
        Dim strSQL As String
        Dim strFilter As String
        Dim strResult As String
        
        Set CN = CurrentProject.Connection
        Set RS = New ADODB.Recordset
        strSQL = "SELECT DISTINCT Locations.ID FROM Locations WHERE ID Is Not Null and (" & Me!cboSelectField & "='" & Me!txtSearchText & "'" & ")"
        RS.Open strSQL, CN, adOpenStatic, adLockReadOnly, adCmdText
    
        
        If RS.RecordCount = 0 Then
            MsgBox "None!"
            Me.cboSelectField.Enabled = True
            Me.txtSearchText.Enabled = True
            Me!btnSearch.Value = 0
            Me!btnSearch.Caption = "Search"
        Else
            Do Until RS.EOF
                strResult = strResult & ", " & RS!ID
                RS.MoveNext
            Loop
            strResult = "(" & Mid(strResult, 3) & ")"
            strFilter = "ID In " & strResult
            FRM.Filter = strFilter
            FRM.FilterOn = True
            Me.cboSelectField.Enabled = False
            Me.txtSearchText.Enabled = False
        End If
    Else
        Me!btnSearch.Caption = "Search"
        Me.cboSelectField.Enabled = True
        Me.txtSearchText.Enabled = True
        FRM.Filter = ""
        FRM.FilterOn = False
    End If
    Set FRM = Nothing
End Sub

 

[Zathras]

To use wild cards, you must use the LIKE operator instead of the equal sign:

Select * from whatever where field LIKE '*4089 Main*'

 

[Dodgey]

You are talking about the 'LIKE' operator. Amend the sequel statement below to:

strSQL = "SELECT DISTINCT Locations.ID FROM Locations WHERE ID Is Not Null and (" & Me!cboSelectField & " LIKE '*" & Me!txtSearchText & "*'" & &quot"

This will return all instances where the field contains '4089'.

Alternatively, you may remove the requirement for the user to specify the address, city or state by expandng the sequel statement to:

strSQL = "SELECT DISTINCT Locations.ID FROM Locations WHERE (ID Is Not Null and (Locations.Address LIKE '*" & Me!txtSearchText & "*')) OR
(ID Is Not Null and (Locations.City LIKE '*" & Me!txtSearchText & "*')) OR
(ID Is Not Null and (Locations.State LIKE '*" & Me!txtSearchText & "*'))"

From what you've described, I'm positive you're aptitude will allow you to solve any problems encountered, as this is untested Sequel.
NOTE: This may affect the performance, significantly if you a searching a very large recordset. If this is an issue, revert back to getting the user to specify the search field.

Food for thought. Please Please mark this as helpful. If it was of course!

 

[khurley]

My code isn't bombing anymore but its not returning any hits on any of the fields. all of my searches on positive data are returning the msgbox NONE!

meaning it found no hits.

Any ideas?

 

[khurley]

PS. THANKS FOR RESPONDING SO QUICKLY

 

[Dodgey]

the recordcount property is well renown for being flakey at the best of times. Instead replace the line with:

"If RS.eof and rs.bof Then"

If this doesn't help, take the value of strResult and paste into a queries SQL statement. This should help no end.

also,...

After the Do loop you have the following line:

strResult = "(" & Mid(strResult, 3) & &quot"

I am sure that the Mid function requires another parameter, which is the length of the string to return. i.e

Mid(text, startPos, lengthToReturn)

If you are checking the strResult for the detail, this may be your problem.

Be keen to hear how you got on.Dodge->

 

[khurley]

In my testing with the code you provided with the asterisks I am not getting any records. Its not returning any records... Any chance We are missing a quote or tick mark or something? I tried change to the EOF/BOF as you suggested.... Thanks!!

I am definitely not getting any returns on either of the sameples with the asterisks.

Let me know what you think ..... These darn asterisks !!!

 

[Zathras]

Try putting

  MsgBox strSql

after you stuff the string to see what you are really getting. You may be messing up the string building.

 

[khurley]

when i use the asterisks as above the MSGBOX StrSql looks like this:

Select DISTINCT Locations.ID from Locations WHERE ID is Not Null and (City like '*freehold*')


So that looks like its correct yes??

But i get no results returned...

 

[Zathras]

Case-sensitive, maybe? is it Freehold?

 

[khurley]

when i don't use the asterisks Case isn't an issue...

I don't think that should be whats bombing...

i just think it must be the syntax with the asterisks...

Not sure how to do the code to just query the table and stick those results through the code... the query works with asterisks.....
Hmmmm

 

[Zathras]

What version of Access are you using?

The SQL standard wild cards are % and _ (percent sign and underscore), however Access 97 seems to want to use the * and ? (asterisk and question mark - holdover from the old DOS days??)

You might try the percent sign if nothing else seems to work.

 

[khurley]

The percent sign is definitely working better. But I am still having a weird thing happen...

When i changed the line to select Where City = Free%

i got the following returned records:

Freehold
Freehold
New York


Why would it pick up New York???

I have the same problem on my address field.

If i put in address = 40%

i get a lot of records but they are all over the place...

70 main street
4089 main street (which is ok)
314 french street

etc....

any more ideas??

I am almost there i think!!!

Kathy