Question Building a Search Form - Dealing with multiple AND / OR operators

[darbid]

I am currently trying to get my mind around making a search form which offers users the most flexibility. I will have at least 30 to 35 text and boolean type fields.

The problem I have is dealing with multiple AND / OR operators on the form AND then how I build the WHERE part of the query.

Ultimately I would like to give users the ability to use AND and OR for each field but I am not sure that is possible. Do you have any suggestions here?

When building the query should I be kind of grouping the ANDs together and the ORs.

eg

SELECT *
FROM myTable
WHERE (all ANDs in here) AND (all ORs in here);

Thank you in advance.

 

[smig]

I do it with a long WhereClues string.
it look somethign like this:

dim strWhereClues as string
dim strQry  as string
 
strWhereClues = "WHERE IsNull([MyKey]) "       ' -- this will never be true, only to make something for start
 
if IsNull(Me.MyFirstCheck) = false then
strWhereClues = strWhereClues  & "AND [MyFirstField] = " & Me.MyFirstCheck & " "
end if
 
if IsNull(Me.MySecondCheck) = false then
strWhereClues = strWhereClues  & "AND [MySecondField] = " & Me.MySecondCheck & " "
end if
 
....
 
strQry = "SELECT * FROM myTable " & strWhereClues

 

[vbaInet]

This is a rather involving task.

I suppose you would have to build the string in the sequence in which the ANDs and ORs was selected.

It will ne nice to show in a textbox the result of the WHERE part of the sql string in a locked textbox so that the user can see whether something is missing or not.

It would also be useful to be able to advise whether the combination of ANDs and/or ORs is a wise selection. This is of course something that the Jet engine knows best. This will be your main hurdle. If the user selected the wrong combination of ANDs and ORs then they simply won't get any result.

A count of how many ANDs and ORs in two other texboxes will be a nice feature.

Finally, it would be nice to be able to highlight text in the textbox and have it changed by making and confirming certain selections.

Basically, what you're trying to achieve here is what the Query designer already does.

 

[darbid]

Basically, what you're trying to achieve here is what the Query designer already does.

Agreed but this is an attempt to give users the ability to do it as best as possible - especially when they do not care about the designer nor have any idea of the column names.

 

[JANR]

Something like this but more expanded?? : http://www.fontstuff.com/access/acctut18.htm

JR

 

[Lightwave]

I think the code should be relatively easy I think what you may find difficult is setting it up in a form so it looks clean. I suspect because the search strings are boolean you will have to have similar number of search fields as columns and at 35 that sounds to me like it might be clunky.

If all of the columns are boolean I think I would be tempted to have buttons and pre-define the queries behind each. That way maybe have 10 predefined queries which are the most common.

This may not be appropriate for your task at hand I don't have a full understanding of the information.

The below code sets up a query using OR that takes one text field and searches throughout a number of fields for the text string. The structure of your queries should be the same and AND could replace OR as required.

REMEMBER if you are struggling with the actual syntax of the SQL you can design using the Visual Query Editor and then skip to SQL view to see what has been created. Brackets will be fiddly if you have that many columns there may also be limits to how many ANDs and ORs you can do.

[Private Sub Text2_Change()

Dim strSQL As String
Dim txtSearchString As String

txtSearchString = Me!Text2.Text

strSQL = "SELECT [Q032FindName].[PKID], [Q032FindName].[Name], [Q032FindName].[Position], [Q032FindName].[Vocation], [Q032FindName].[CompanyName] FROM [Q032FindName]"
strSQL = strSQL & "WHERE ((([Q032FindName].[Name]) Like '*" & txtSearchString & "*') OR (([Q032FindName].[CompanyName]) Like '*" & txtSearchString & "*') OR (([Q032FindName].[Position]) Like '*" & txtSearchString & "*') OR (([Q032FindName].[Vocation]) Like '*" & txtSearchString & "*') OR (([Q032FindName].[PKID]) Like '*" & txtSearchString & "*'))"

Me!List11.RowSource = strSQL
Me!List11.Requery


End Sub

 

[CBrighton]

I'd do it completely in VBA, similar to Smig's suggestion.

I'd build the most basic query possible for the form itself and apply a string with all the where clauses to the filter property of the form / subform displaying the results.

On things like date fields you can add a combobox which contains things like "On;Before;After,Between" along with 2 textboxes to hold dates. The after update event on the combobox can enable / disable the second date textbox and a nested If statement can deal with each situation differently.

Fields like ID / Name are simple enough but for somefields it may be worth adding a check box to change the "=" to "like", for example this would allow you to search for all records in an area code by searching on a phone number field.

 

[smig]

not sure how you'r going to set the AND and OR on the form.
I think it will totaly confuse the users, as the placement of them can make a huge different:
str1 AND str2 OR str3 NE str1 OR str2 AND str3

you should design your form carefully

if you realy wish to confuse your users add the options for NOT

you can use my code like this, but results are not predicted

dim strWhereClues as string
dim strQry  as string
 
strWhereClues = "WHERE IsNull([MyKey]) "       ' -- this will never be true, only to make something for start
 
if IsNull(Me.MyFirstCheck) = false then
strWhereClues = strWhereClues  & AND_OR_1 & " [MyFirstField] = " & Me.MyFirstCheck & " "
end if
 
if IsNull(Me.MySecondCheck) = false then
strWhereClues = strWhereClues  & AND_OR_2 & "[MySecondField] = " & Me.MySecondCheck & " "
end if
 
....
 
strQry = "SELECT * FROM myTable " & strWhereClues

if your users are sophisticate enough you can let them built their oun formula, by selecting the field add criteria, add the And/Or, EQ/NE/GT/LT/GE/LE, and ")"/"("
so basicely they will create the query

 

[darbid]

you should design your form carefully

if you realy wish to confuse your users add the options for NOT

Yep I am doing the form and for boolean I am going to have "True / Flase / Ignore"

Something like this but more expanded?? : http://www.fontstuff.com/access/acctut18.htm

JR

Yes that is what I am trying to achieve. Instead of buttons I am just going to have clickable captions which on click will revolve from "AND" to "OR"

I think the code should be relatively easy I think what you may find difficult is setting it up in a form so it looks clean. I suspect because the search strings are boolean you will have to have similar number of search fields as columns and at 35 that sounds to me like it might be clunky.

I am thinking that I can "Group" some things together for the SQL and thus for the form. These groups would then have an Operator set. Thus cutting down on the possibilities.


But From the responses it seems that there is not much out there on an easy way to do this. It seems it is very much user specific.

I dont want to get too advanced with the form set out until I get some theory on doing it.

 

[smig]

as much as I think of it I think letting the user create his oun qury will be the easiest solution.

here how I see it:
1. a combo box with all fields you wish.
2. you have a second combo with options like =, <>, >, <, <=, >=
3. a box (either a text box or a combo with True/False)
4. a button "Add"
5. an option to add OR/AND
6. a big text box that show the result
7. a hidden text box that show the real query (with full and real name of field)

this is how it will work:
the user select a field, select a =, <>, >, <, <=, >= (if he selected a boolean field he can only select = ) and set box 3 (he can leave as null. in case of boolean field he can only put True/False)

now he click the add button and the query is started to built.
next he's inforced to select either OR or AND before he can add the next field.....
he can also select part of the big text box and click to add brackets

at start users will be a bit of confused, but once they will start using it I'm sure they will like it.


in the first combo you put these fields:
1. a readable name for the user
2. the real name or the field for the hidden text
3. the type of field (this is importent to control the options for next fields)
4. Optional - options for 3rd box

example:
1. Birth Date | [tblMyData].[BirthDate] | Date
2. Year of date | Year([tblMyData].[BirthDate] | Integer
3. Sex | [tblMyData].[Sex] | String | "Male,Female"

 

[darbid]

Thanks smig I will keep that in mind.

My conclusion is that users do not need and/or do not understand complex searches and thus as a designer you cannot give them too much.

I am going to go back to the drawing board and limit the operators for users.

 

[smig]

don't give it up
I think you should try what I suggested. there are many options here.

if you ommit the OR option and only keep AND it will be far easier both for you and users (also no need for brackets)

so the user can easy select 30 years old, unmeried, females live in Texas.

as much as I think of it more I love my idea more

in the first combo you can also specify more columns that will give information for 3rd. selection. things like query to take data from, list of values.....

if you keep it for AND only you can put what user selected in a list box one on top of the other, so he can easily see what he did, and even remove a criteria

truth I think it's not complicate, espacially if you ommit the OR option.
carefull design of the first combo is the key here

 

[darbid]

Thanks mate.

By the way I would change that to

30 year old AND (unmarrie OR divorced) AND no kids AND live in Texas

 

[smig]

we better leave the Texas women alone, or they'll kick us in the ass with their cowboys boots

 

[vbaInet]

You want to create a place where users can build their WHERE conditions. You have 30+ fields to base this on. There was a reason why the query builder implements a row for Criteria input because it is intuitive. This sort of fashion is implemented in some other SQL builders.

If you really want to create something intuitive then you're better off following the same style. If you had 5 fields or less then it will be sensible to try to come up with something different.

 

[darbid]

yeh I agree. I am really trying to replace the query builder and thus am onto a bad idea. Users would not understand the query builder so I am dreaming if I am going to be able to make it easier.

I have groupd fields and set the Operators based on my existing thoughts and example queries that need to be built.

Thanks guys for your help.

 

[smig]

I took my oun advice and started to create this for my oun application

attached a very early start
as you can see the main part is the main table. everything is relying on this. code is rather simple.

for some fields you can use multi select for other you can't.
text fields are not on form yet, and they will be added later.

going back to our Texan women I think in most cases you won't ask a question like:

Unmeried OR divorced OR has blonde hair

in my system you can ask a question like

has a blonde hair AND (Unmeried OR divorced)

(Unmeried OR devorced are on the same table, and can be done with the multiselect)