Function Like "*" Search

[steve21nj]

I am running a search function that works, but is limited in one of my fields.

If I want to search for "B", and search every title with an "B" in it (regardless of where the B is), it wont show. If I have a title that is labeled "BAC", it will not show if I search "B", unless I type "BAC".

Can someone assist?

Sample Code

Private Function AdvancedSeach() As Variant
  Dim varWhere As Variant
  Dim tmp As String
  tmp = """"
  varWhere = Null
 
'Title
If Me.Text240 > "" Then
  varWhere = varWhere & "[MyField] like " & tmp & Me.Text240 & tmp & " AND "
  End If
 
If IsNull(varWhere) Then
  varWhere = ""
Else
  varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
  varWhere = Left(varWhere, Len(varWhere) - 5)
  End If
End If
    AdvancedSearch = varWhere
End Function

 

[namliam]

you arent using any wildcards (* in this case) .... no wildcards makes your query fail...

varWhere = varWhere & "[MyField] like *" & tmp & Me.Text240 & tmp & "* AND "

FYI I can advice you dearly to not keep using default names like "Text240", instead make sure to give your controls meaningfull names

 

[steve21nj]

Thanks for the name suggestion, I built this database as a sample for another db to see if I could get it to work.

When I make the change "*" I get a run-time error as pictured in attachment.

'MyTitle
If Me.Text240 > "" Then
  varWhere = varWhere & "[MyField] Like *" & tmp & Me.Text240 & tmp & "* And "
  End If

 

[namliam]

Obviously, the * needs to be inside the " " part, thus inside your tmp variables.

 

[steve21nj]

Currently the * is inbetween the "". I've moved the "" and * around but keep getting errors. I'm sure this is something really stupid but I haven't figured it out yet.

'MyTitle
If Me.Text240 > "" Then
  varWhere = varWhere & [COLOR=red]"[MyField] Like *"[/COLOR] & tmp & Me.Text240 & tmp & [COLOR=red]"* And "
[/COLOR]  End If

 

[pr2-eugin]

varWhere = varWhere & "[MyField] Like [COLOR=Red][B]'[/B][/COLOR]*" & tmp & Me.Text240 & tmp & "*[COLOR=Red][B]'[/B][/COLOR] And "

 

[namliam]

No PR2, his tmp vars are ", thus...

varWhere = varWhere & "[MyField] Like "& tmp & "*" & Me.Text240 & "*" & tmp & " And "
or
varWhere = varWhere & "[MyField] Like ""*" & Me.Text240 & "*"" And "
or (what PR2 meant)
varWhere = varWhere & "[MyField] Like '*" & Me.Text240 & "*' And "

 

[pr2-eugin]

No PR2, his tmp vars are ",

Ah ! missed that bit Thanks for correcting me. You are right on track !

 

[Cronk]

The other issue with this code is that the WHERE condition has 'AND's for the fields. This means that the search string has to be in every field to get a match.

Replace them with ORs