Apply filter

[Mark]

Hi all,
A form based on a query (qry_source_trailers) contains the field
[bookref]. In the forms header, I have placed an unbound text box where the
afterupdate property is set to:
DoCmd.ApplyFilter , "bookref = forms!frm_trailers!txt_find_bref" This works
fine but I would like to change the filter property to Like instead of
equals due to some of the booking references being very long.

I've tried DoCmd.ApplyFilter , "bookref like " *
"&forms!frm_trailers!txt_find_bref&" * "" which throws up a type mis-match.
Could anyone offer any guidance?

TIA,

Mark

[Allen Browne]

Assuming bookref is a Text field (not a Number field), try:
Dim strWhere As String
strWhere = "bookref like """*" & forms!frm_trailers!txt_find_bref &
"*"""
DoCmd.ApplyFilter , strWhere

The odd quotes are because you need to end up with a string that looks like
this:
bookref Like "*peace*"
and you have to double-up the quotes when they are embedded. To help get it
right, you could:
Debug.Print strWhere
and then look in the Immediate Window (Ctrl+G) after it runs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" <ma*********@ntlworld.com> wrote in message
news:Tl*************@newsfe1-gui.ntli.net...

A form based on a query (qry_source_trailers) contains the field
[bookref]. In the forms header, I have placed an unbound text box where
the afterupdate property is set to:
DoCmd.ApplyFilter , "bookref = forms!frm_trailers!txt_find_bref" This
works fine but I would like to change the filter property to Like instead
of equals due to some of the booking references being very long.

I've tried DoCmd.ApplyFilter , "bookref like " *
"&forms!frm_trailers!txt_find_bref&" * "" which throws up a type
mis-match.
Could anyone offer any guidance?

[Mark]

Many thanks for your help and explanation again Allen. Worked a treat.

Regards,

Mark

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Assuming bookref is a Text field (not a Number field), try:
Dim strWhere As String
strWhere = "bookref like """*" & forms!frm_trailers!txt_find_bref &
"*"""
DoCmd.ApplyFilter , strWhere

The odd quotes are because you need to end up with a string that looks
like this:
bookref Like "*peace*"
and you have to double-up the quotes when they are embedded. To help get
it right, you could:
Debug.Print strWhere
and then look in the Immediate Window (Ctrl+G) after it runs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" <ma*********@ntlworld.com> wrote in message
news:Tl*************@newsfe1-gui.ntli.net...

A form based on a query (qry_source_trailers) contains the field
[bookref]. In the forms header, I have placed an unbound text box where
the afterupdate property is set to:
DoCmd.ApplyFilter , "bookref = forms!frm_trailers!txt_find_bref" This
works fine but I would like to change the filter property to Like instead
of equals due to some of the booking references being very long.

I've tried DoCmd.ApplyFilter , "bookref like " *
"&forms!frm_trailers!txt_find_bref&" * "" which throws up a type
mis-match.
Could anyone offer any guidance?