Wildcard to filter form

  • Thread starter Thread starter DeVille
  • Start date Start date
D

DeVille

Hi I have this piece of code which uses an 'on-click'
event procedure on my form to filters my records. The
field is named 'Subject' and the code brings up a
dialogue box which I then type the first part of a
sentence and it finds all records which start with that
text. Eg if I type 'TH' it would bring back all records
that start with words like 'THE', 'THERE' or 'THAT' etc.
I would like to change the code so that it finds records
where the wild card is in the middle of the text. Eg if I
typed "OO' it would find 'BOOK' or 'SCHOOL' etc.
If any one can help me thanks in advance. see code
below

Let CurRecNo = Form.CurrentRecord
QryFld = " Subject "

Me.Filter = "[Subject] Like '" & InputBox("Enter
Subject") & "*'"
Me.FilterOn = True
 
I would like to change the code so that it finds records
where the wild card is in the middle of the text. Eg if I
typed "OO' it would find 'BOOK' or 'SCHOOL' etc.
If any one can help me thanks in advance. see code
below

Let CurRecNo = Form.CurrentRecord
QryFld = " Subject "

Me.Filter = "[Subject] Like '" & InputBox("Enter
Subject") & "*'"
Click to expand...

A LIKE clause uses wildcards: * means "match any string of zero or
more characters", ? means "match any single character", # means "match
any digit", etc. So if you want any string of characters, followed by
a selected string, followed by any other characters you just need a *
before and after the subject:

"[Subject] Like '*" & InputBox("Enter Subject") & "*'"

If the user types in OO this will be expanded to

[Subject] LIKE '*OO*'

and will match as you requested.
 
Back
Top