"LIKE" predicate not working as expected 2

[sumoalex]

Hi:
This is a weird problem. I have a field on the table "Street Name" which contains street name and suffix (e.g. Marsh Street).

I use the LIKE predicate as follows:

Me.Filter = "[Street Name] like ""*" & strInput & "*"""

On the table, I have some records with "Marsh St.", "Marshall Dr."

When doing a search where strInput = "Marsh":
1. IF the first found record in the table is the record with "Marshall", it lists all the "Marshall"s
2. IF the first found record in the table is the record with "Marsh", it filters on all the "Marsh"s

Is this weird, or is it me? I thought it's supposed to just find anything with "March" in the field! ARGH!

 

[judgehopkins]

Instead of

Me.Filter = "[Street Name] like _
""*" & strInput & "*"""

try

Me.Filter = Like "[Street Name] * " _
& strInput & "*"""

But, alas, I am confused. Are you using LIKE in a query? LIKE is a query criteria.

In fact, the code above may not work since you cannot use LIKE plus a wildcard.

http://support.microsoft.com/defaul...port/kb/articles/q163/8/93.asp&NoWebContent=1

Also, see faq701-2330

On the other hand, this has been known to work:

[Contacts].[FirstName]) Like '*' & [Forms]![nlContactsMain]![FirstName2] & '*'

http://www.experts-exchange.com/CXO/Databases/MS_Access/Q_20536845.html#8059477

and

http://www.experts-exchange.com/Databases/MS_Access/Q_20525110.html

How to use wildcards:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima0601.asp

Here is the code I use for searching a combobox on a form:

Private Sub cmdGoToRecord_Click()
On Error GoTo Err_cmdGoToRecord_Click
Dim intCount As Integer
Dim intNumberColumns As Integer
Dim strFind As String
strFind = InputBox("Please enter item to find", _
          "Search", vbNullString)
    If strFind = "" Or strFind = vbNullString Then
        MsgBox "You didn't enter anything!", vbOKOnly, _
           "Nothing to look for!"
        Exit Sub
    ElseIf strFind <> vbNullString Then
      With lstGoToRecord
        For intCount = 0 To .ListCount - 1
            For intNumberColumns = 0 To .ColumnCount - 1
                If InStr(1, .Column(intNumberColumns, 
                      _ intCount), strFind) > 0 Then
                   MsgBox &quot;This record has been found!&quot;, _
                      vbOKOnly, &quot;Search successful&quot;
                    ' this has been found
                    .Selected(intCount) = True
                    Exit Sub
                End If
            Next intNumberColumns
        Next intCount
        MsgBox &quot;This record has not been found!&quot;, _
           vbOKOnly, &quot;Search failed&quot;
        ' this has not been found
        End With
    End If

Exit_cmdGoToRecord_Click:
    Exit Sub

Err_cmdGoToRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdGoToRecord_Click


End Sub

"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air

 

[judgehopkins]

Another study aid:

http://www.faqts.com/knowledge_base/view.phtml/aid/6297/fid/14

"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air

 

[scriverb]

Try this. It will prompt you for the value of strInput. Type in Marsh and it selects all records with "marsh" in the name.

Me.Filter = "[Street Name] Like '*' & strInput & '*'"


Post back with any questions please.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[sumoalex]

I just tried that and I still get only the 3 "Marshall"s.

That's why this is freaking me out. There's no rhyme or reason for this except that, if a "Marsh" occurs earlier in the database than a "Marshall", it'll pick up all of the "Marsh"s.

 

[sumoalex]

I guess since I'm at the end of my rope with this, I need a suggested alternative.

I have a main form where the user inputs the search criteria (txtSearchStreet). I need to do a "like" match on the "tblContractApplication.Street Name" field and display the results on subFrmApplication.

Any input would be greatly appreciated.

 

[Rolliee]

I just used this to pick out all the leading and embedded last names that had a "man" in them. If this can be done, the SQL can be likewise built to do the same. That owuld appear more straighforward to me.

Code:
SELECT HHOLD.HID, HHOLD.LName, HHOLD.FName, HHOLD.Salu, HHOLD.Addr, HHOLD.City, HHOLD.ST, HHOLD.ZIP, HHOLD.Phone1, HHOLD.Phone2, HHOLD.NewsLtr, HHOLD.IsChurch, HHOLD.IsOrg, HHOLD.email, HHOLD.Code
FROM HHOLD
WHERE HHOLD.LName LIKE '*man*';
end code:

 

[sumoalex]

Yes the following SQL returns everything with Marsh in it (Marsh, Marshall):

SELECT tblContractApplication.ContractID, tblContractApplication.[Street Name]
FROM tblContractApplication
WHERE (((tblContractApplication.[Street Name]) Like '*' & "Marsh" & '*'));

but I was trying to use "like" in a filter and it doesn't seem to want to work properly as a filter. So I"m scrambling to recode it a different way.

Do I create a recordset with the sql above and then send that to the subform somehow?

 

[sumoalex]

HERE IS MY EXISTING CODE (hopefully someone can spot something wrong in it):

Private Sub txtSearchStreet_AfterUpdate()
On Error GoTo Err_Handler
Dim strInput As String

If IsNull(txtSearchStreet) Or txtSearchStreet = "" Then
MsgBox "Please enter a value to search on and press Enter"
Exit Sub
End If

strInput = Trim(Me.txtSearchStreet)

Me.Filter = "[Street Name] like ""*" & strInput & "*"""
Me.FilterOn = True

Me.RecordsetClone.FindFirst "[Street Name] like ""*" & strInput & "*"""

If Me.RecordsetClone.NoMatch Then
Me.FilterOn = False
subfrmFindWorkRecord.Visible = False
MsgBox "Record not found.", vbInformation, "Search Error"
Me!txtSearchStreet = Null
Me!txtSearchStreet.SetFocus
SendKeys "+{TAB}"
Else
subfrmFindWorkRecord.LinkChildFields = "Street Name"
subfrmFindWorkRecord.LinkMasterFields = "Street Name"
subfrmFindWorkRecord.Visible = True
End If

DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst

Exit Sub
Err_Handler:
MsgBox Err.Description
End Sub

 

[TonyJollans]

Hi sumoalex,

I'm afraid your code looks like it will do as you describe in your first post and I'm not sure of the best way to change it.

It looks like you are trying to apply a filter to a subform. Your subform Link Master will, however, be the first value found and then the records found in the subform will only have that value.

What are your Record Sources for your Main and Sub Forms?

Enjoy,
Tony

 

[sumoalex]

Hi Tony,

Thanks for your response.

The record source for both forms is "tblContractApplication".

When you say "Your subform Link Master will, however, be the first value found and then the records found in the subform will only have that value." what should I change them to in order to get all the matches?

 

[TonyJollans]

Hi sumoalex,

You could try removing the master and child links and setting the filter of the subform instead:

Me.subfrmFindWorkRecord.Form.Filter = "[Street Name] like ""*" & strInput & "*"""
Me.subfrmFindWorkRecord.Form.FilterOn = True

Enjoy,
Tony