How to construct a BuildFilter

[DBDave]

I'm trying to create a search form. The backend SQL table I need to search is partly denormalized. It contains 2 person name fields, something like [SalesmanName] and [ManagerName]. I have a union query which generates a list of all names (without dupes) including Salesmen and Managers because I don't care which of the two fileds contains the name being searched for. The user selects a name from a unbound combobox which has the union query as it's row source.

The code for my search form sets a RecordSource as in the following common example:


Me.RecordSource = "SELECT * FROM qrySearch " & BuildFilter

If I want to construct BuildFilter as a "Like" clause, that's a piece of cake. There are a number of examples that show how to do that. However, I want to construct a BuildFilter with an equality ("="), and also using "or" operator? In pseudo-code, that looks like the following:

"If the comboxbox value is not blank find rows where [SalesmanName] equals the combox value OR [ManagerName] equals the comboxbox value"

I'm stuck on constructing that statement. All my attempts at that syntax are blowing up with compile errors or syntax errors. Can you help me out? I would greatly appreciate it!

[ssanfu]

This example should help you. If not, post back and ask more questions.

http://www.allenbrowne.com/ser-62code.html

[DBDave]

This example should help you. If not, post back and ask more questions.

http://www.allenbrowne.com/ser-62code.html

Great examples in that article. Thanks!

[DBDave]

This example should help you. If not, post back and ask more questions.

http://www.allenbrowne.com/ser-62code.html

Can I follow up with a related question? I set up the filtering on a search form and now it's working with help of that Allan Browne article. I want the user to click a button and open a report based on the same filter. What's the best approach to that? Do I have to recreate the same filter function in an event on the report? Can the report refer to the BuildFilter in the form? Thanks.

[June7]

Instead of (or in addition to) setting the form filter property, pass the filter criteria to report when it opens:

DoCmd.OpenReport "report name", , , strWhere

If you want separate button for opening report, then put Allen Browne's code into a Sub behind the form that can be called by both buttons.

[DBDave]

Instead of (or in addition to) setting the form filter property, pass the filter criteria to report when it opens:

DoCmd.OpenReport "report name", , , strWhere

If you want separate button for opening report, then put Allen Browne's code into a Sub behind the form that can be called by both buttons.

Great. I'll try it tonight.