Form to search using and/or parameters

[doobybug]

Hi,

I am planning to have a form that acts as a query to search my table. I was thinking of doing the multiple fields and give the option to search by one or multiple fields along with the option to choose whether they want an AND criteria or an OR criteria. Ex: Search by name and surname
Search name or surname

Any ideas how I can achieve this? Maybe you can link me to an example?

[June7]

If you allow only one operator then should be simple. Multiple AND or multiple OR operators is easy. Mixing AND and OR could be really nasty.

I have only done this once to allow OR operator. And never was satisfied with it.

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

[doobybug]

I have uploaded a picture of how I was thinking that the form should look like...I was going to include radio buttons or a check box for the and/or button. So you think I should abandon the idea?

[June7]

Mixing AND and OR operators gets complicated. Have to get parentheses just right.

A AND B OR C

is not the same result as

A AND (B OR C)

[doobybug]

Thanks I will keep it in mind!! I understand what you are saying...I will try to avoid the OR then

[Micron]

If you use radio buttons, be sure to put them into an option group, otherwise you'll be able to select both. I was working on a db with a search form, but my intention was to have it much more robust than what you've shown. Alas, I got distracted by life (and maybe spending too much time here answering questions ) and haven't gotten back to it.

What you want can work, but it requires a lot of thought and coding. As June7 implies (I think) and/or can be tricky but if you are not trying to nest them, prevent unsuitable choices from being made and don't allow conflicts then I think they should work fine. Search forms might function better if combo's are used instead of text boxes for certain fields so that garbage can't go into the search. A name field is probably not a good one for that. Once you have the design, the hard part comes. You have to concatenate criteria if there's more than one possibility and deal with situations where the first is supplied, but not the second (or vice versa) to avoid getting no results. You might also code for the possibility that incorrect data types are entered.

Another method is to search as you type - filter the records displayed according to a search text box by using the OnChange event, but again, you'd have to concatenate the filter expression if it involves more than one field. That method allows the user to immediately see the result after each character is typed in since invalid criteria results in no records being displayed.

[June7]

cross-posted https://access-programmers.co.uk/for...d.php?t=293875

[Micron]

Thanks June7. I suggest another step to try to curb the practice. Doobybug, please read:
http://www.excelguru.ca/content.php?184