'Like' Filter problems

[daybase]

For the first time I am trying to use Like in VBA to do a flexible search on a database and to a point it works... but only to a point and I am sure that I must be missing something obvious.

By way of explanation I am looking to open a form which contains a reference, a property address and a tenant. The input Street will successfully find all records with Street in the address. Market or Market Street will find all Market Street properties BUT 25 or 25 Market Street will not find a record despite it showing in the other searches. Likewise Jones will find all tenants with that surname, D Jones narrows it down to all of that name BUT Mr D Jones will not find a record despite it showing in the other searches.

So please where am I going wrong?

Private Sub GetMyFile_Click()
On Error GoTo Err_GetMyFile_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Find Property"
    stLinkCriteria = "[PropRef] like" & "'*' &' " & Me![WhichFile] & "' &'*'"
    stLinkCriteria = stLinkCriteria & " or [PropAddress] like" & "'*' &' " & Me![WhichFile] & "' &'*'"
    stLinkCriteria = stLinkCriteria & " or [PropTenant] like" & "'*' &' " & Me![WhichFile] & "' &'*'"

 

[Golom]

Your version will result in a string like

[PropRef]     like'*' &' ABC' &'*' or 
[PropAddress] like'*' &' ABC' &'*' or 
[PropTenant]  like'*' &' ABC' &'*'

if Me![WhichFile] has the value "ABC"

If you use

stLinkCriteria = "[PropRef]     like '*" & Me![WhichFile] & "*'" & _
             " or [PropAddress] like '*" & Me![WhichFile] & "*'" & _
             " or [PropTenant]  like '*" & Me![WhichFile] & "*'"

Then you get

[PropRef]     like '*ABC*' or 
[PropAddress] like '*ABC*' or 
[PropTenant]  like '*ABC*'

which is how it should look.