Enable "and/or" on custom search form

[DB88]

Hello,

I've created a custom search form where using unbound text boxes and combo boxes I enter the criteria for a query. I would like to enhance my form to allow "and" and "or" searches on a field. For example, a product on my database has multiple features. I would like to search for products who have "feature1 and feature 2" or "feature1 or feature2". {Note: The table, Product is connected to table, Features using table, LineItem_ProdFeature where there are multiple records in Features for one record in Product}

Right now, the search form has a combo box that lists all product features. The user selects a feature and runs the query. All products with that feature are listed in the query.

It would be so great if the user could choose multiple features from the combo box. Then the query would return all products that have all of the selected features ("and" search). It would be just awesome if the user could select multiple features, and specify if they want an "and" or "or" search of those features.



I'm an intermediate user with no knowledge of Visual Basic. I'm also on a time crunch, so if this is too envolved or advanced then please let me know. The tricked out combo box would be great but a simpler solutions are also welcomed.

Thanks

[ranman256]

That's a lot to do for a beginner, besides, you are trying to recreate that Access already does. The QBE grid lets you pick fields and put in AND or ORs, so you are re-inventing the wheel that is sitting in your garage.

If you must,
I would provide the fields you have ,then the users would build the WHERE clause. They would pick the existing fields and the ANDs or ORs.
I tried it once but the ands/ors can get complex and bring the wrong data.

Esp. if you're a novice. QBE is ready and easy.

[DB88]

I'm not trying to reinvent the wheel, I'm trying to create a user interface for users who know nothing about Access. My users can't build their own queries. They don't even understand how databases work i.e relating records from multiple tables.

If creating an enhanced combo box is too complicated that is ok. My other idea is to just create a 4 unbound text boxes and hard code in the or/and using these test boxes in the query. It is crude and limited but it will work for now. Any advice on this?

[rpeare]

if you are creating your SQL on the fly already can you not just substitute 'and' if the and box is checked, 'or' if the or box is checked?

for instance

sSQL = "SELECT * FROM tablex WHERE "
if chk_OR = -1 then
    sSQL = sSQL & "[Feature1] = '" & textbox1 & "' OR [Feature2] = '" & textbox2 & "'"
elseif chk_AND = -1 then
    sSQL = sSQL & "[Feature1] = '" & textbox1 & "' AND [Feature2] = '" & textbox2 & "'"
else
    'do whatever you do if neither is checked
endif

[DB88]

Yes, VIP that is the idea.

Two questions:
1. Can I do this using the design view interface or do I have to write SQL code?
2. Can I enable my combo box to hold multiple values? If yes, how? Also, if yes, how would the code change to accommodate a varying number of input values (aka could be 0, 2, 6 etc numbers of features that are desired)?

[rpeare]

here's the problem with doing this kind of function, it's easy for 2 values, and becomes geometrically harder to program a SQL statement because you can have

Option A OR Option B OR Option C
Option A OR (Option B AND Option C)
(Option A AND Option B) OR Option C
(Option A OR Option B) AND Option C
Option A AND Option B AND Option C

etc

in other words if you add 6 features you're going to have to program for each possibility of AND/OR which is 2^6 or 64 possible option setting.

This would not work well with a query design though it is possible if you are going to limit yourself to 2 or 3 features, you could actually write a formula in the query design window that is basically a SHOW boolean value but are harder to handle if you're not comfortable with tracking down errors in, what can be, very complex formulas.

[June7]

Agree with rpeare. Parens are critical when mixing AND and OR operators. The more parameters the more complication.

Your users would have to understand that and manually construct the sequence and select the parens as appropriate - like in a scientific calculator. Misplaced or non-paired parens will produce undesirable results.

[DB88]

To contain the problem, I will provide the user with 3 search fields and only two options ( and & or). So the user can enter up to three search values and select if they want a product with any of those features or all of those features. To simplify further, I will worry about a combo box that can hold up to 3 values later and just put 3 unbound text boxes on my form.

How would I do this in the query design window? If that is too complicated, what would the SQL code look like? Recall, that the user may leave one or all of the search fields blank. That wasn't included in your previous code

[June7]

Can't do this in a single query. Use VBA code to build filter criteria and apply to Filter property of form or for use in WHERE CONDITION of DoCmd.OpenForm (or OpenReport).

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

Adapt code to include conditional construct for the AND or OR options.

[DB88]

Really, Access query can't handle the simple version of 3 fields and 2 options I described? Bummer

I'm on a real time crunch and learning Access as I go. My DB experience is with a different software that I can't use for this project. I was hoping to get my users basic functionality without having to learn VB then release an enhanced version later after I've gained more proficiency. But this is pretty basic so I guess I will just have to bit the bullet and muddle through VB.

Thanks for your help

[June7]

Access query object can be designed to have the AND operators or the OR operators, but AFAIK cannot dynamically change the operators (but then I never tried). That would be 2 different queries. Would need VBA code and QueryDefs collection to modify the query object, in which case might as well just use the code to set the form Filter property.

[DB88]

I've read through the article that you provided. While it is a great start it still doesn't answer my question. It addresses how to search for records in one table based on multiple fields. Aka I want a record in a single table where Field1 = "A", Field2 = "B" and Field3 = "C". What I need is to search for a records in Product who has multiple records in the related table features.

Hopefully the following make sense. It is always hard to describe your problem well:
There is a 1 to Many relationsip between tables Product and Feature. To accomplish this I've created a line item table, LI_ProdFea. Each record in product is related to mulitple records in Feature. For example, my user want to find a product that is battery operated and waterproof. So the query/code/whatever needs to return a record from Product table that has two related records in Features, namely waterproof and battery.

The more I think about what this search form needs to be able to do, the more complex my problem becomes. Unfortunately, my users have no knowledge of Access or databases in general. So when I ask what type of information they want to pull. I get the response "just make all the fields searchable then we can always find what we want" Not a very helpful response.

[rpeare]

it's not simple the way you described it, what I'm trying to impress is that you seem to be using a non-normalized structure so for the purposes of your example you have 3 feature fields and a 'simple' and/or toggle.

How do you know which feature to search?

let's say you have six different items in your table with their feature stored:

Feature1  Feature2  Feature3
A         B         C
A         C         B
B         A         C
B         C         A
C         A         B
C         B         A

you are not just searching one field for feature A you would have to search each field for A in your query On top of that you are adding a level of complexity in the OR/AND switch which potentially doubles your permutations.

There are always ways to do things but the complexity of a formula sometimes outweighs the usefulness.

Let's say you had Feature1 and Feature2 you could conceivably have a formula like:

SHOW: iif([forms]![formname]![chk_OR] = -1, ( OR PART ), iif([forms]![formname]![chk_OR] = -1, ( AND PART ), 1))

where the OR PART or AND PART would be a subsequent formula like

OR PART:
iif(([forms]![formname]![feature1] = Feature1 OR [forms]![formname]![feature1] = Feature2) OR ([forms]![formname]![feature2] = Feature1 OR [forms]![formname]![feature2] = Feature2), 1, 0)

then you'd have to substitute in the OR PART and you can see that very quickly your equation will explode and you will likely bump up against the character limit of a formula.

If you are determined to proceed with a non-normalized structure and an AND/OR search you may want to look into creating your own global function and seeing if you can come up with something that will correctly identify what you want.

Personally, for this sort of thing I would create a run time SQL string and not store a query at all depending on the output desired. Exporting would be a little tricker as would creating a report with the results.

[June7]

Whether or not these 'features' are normalized is a good question.

If the same values can be in any of the 3 fields, as rpeare described, then that is not normalized. An example of this is: Job1, Job2, Job3. This means each record could have 3 jobs (carpenter, accountant, truck driver, etc.). If record 1 has Accountant in Job1 and truck driver in Job2, then if record 2 has truck driver in Job1, that is non-normalized data.

[DB88]

There are not 3 feature fields there are 1. Sorry but I don't think my explanation was sufficent. Let me try again:

The table, Feature has two fields: ID and FeatureName
The table, Product has two field: ID and ProductName
The table, LI_ProdFea has three fields: ID, kf_Feature and kf_Product
Product is connect to LI_ProdFea by ID=kf_Product. LI_ProdFea is connected to Feature by kf_Feature=ID

So, for product1 with ID=1, that is waterproof (ID =10) and battery operated (ID=11) there are two records in LI_ProdFea:
ID kf_Product kf_Feature
--- ------------ ------------
1 1 10
2 1 11

The "or" search is much easier. It just looks for all records in LI_ProdFea that have kf_Feaure = 10 or 11 and returns the corresponding ProductName.
The "and" looks for all Products who have two records in LI_ProdFea, one that is kf_feature=10 and the other that is 11.

I agree it is much more complicated then I initially thought. And after Allen Browne's article, I agree I'm going to need VB. But it is still a simple equation if I limit 3 search text boxes and do only "and/or":
If user selects "or" then equation is ([LI_ProdFea][kf_Feature]=[searchtext1] or [LI_ProdFea][kf_Feature]=[searchtext2] or [LI_ProdFea][kf_Feature]=[searchtext3] )
If the user selects "and" then equation is ([LI_ProdFea][kf_Feature]=[searchtext1] and [LI_ProdFea][kf_Feature]=[searchtext2] and [LI_ProdFea][kf_Feature]=[searchtext3] ) {Note: this is a simplistic view of the "and" problem, I'm probably going to do some looping or concatetation to solve it. I used this just for explanation}