how to write Where Condition to search for word in field

[mdavid]

Hi,
I have a textbox - txtFilter - in the header section of continuous forms, in Events > AfterUpdate for txtFilter I've created a SetFilter macro to search the [Complaint] field on the continuous forms. What is the Where Condition to search for a word anywhere in this field?

I've tried -

[Condition] like '*' & [txtFilter] & '*'

but when I enter a word in txtFilter and press Enter I get a box to "Enter Parameter Value" for txtFilter.



I want only those records that have the search term to appear in the continuous forms

Any help appreciated
David

[June7]

If you want to search the Complaint field then why aren't you referencing it? Also need full form qualifier prefix.

[Complaint] like '*' & Forms!formname![txtFilter] & '*'

[mdavid]

is this what you mean:

[Forms]![MempCompSF]![MempComplaintSubFrm].[Form]![Complaint] Like '*' & [Forms]![MempCompSF]![MempComplaintSubFrm].[Form]![txtFilter] & '*'

This returns no records - no record found with filter term, even though there are records containing the term

[June7]

There are two syntax structures for the WHERE CONDITION. Since this is a subform you want to filter, suggest using:

Where Condition = ="[Complaint] LIKE '*" & [txtFilter] & "*'"

[mdavid]

Thanks for this, been driving me nuts! Your suggestion about referencing and qualifier threw me.

Ok this works great, one more question I would like to search 2 fields like so

="[Complaint] LIKE '*" & [txtFilter] & "*'" OR ="[EditedComplaint] LIKE '*" & [txtFilter] & "*'"

Is this possible - if so what's the format?

Thanks
David

[June7]

="[Complaint] LIKE '*" & [txtFilter] & "*' OR [EditedComplaint] LIKE '*" & [txtFilter] & "*'"

[mdavid]

Perfect, thanks June, learnt alot!

Marked Solved.

As a matter of interest you say "there are two syntax structures for WHERE CONDITION" where's can I see exmple / documentation?

David

[June7]

I did get ideas from web sources then exercised trial and error. Probably can't find them again. I search "Access macro SetFilter". I already show the two structures that worked for me. However, I tested the first one from post 2 on a solo form, not a subform, hence suggestion for second structure.

I don't use macros, only VBA.

[mdavid]

Ok Thanks, this was my first foray into Macros - seemed like a good idea, but now I think I'll stick to VBA as well.

Thanks for all the help
David