Boolean Searches

Looking at your example selections, you'll need:

(1) A parsing module, to separate the conditions into their logical components

(2) A query builder.

Example:

stereo and speakers not radio

The WHERE for this would be:

WHERE MyField LIKE '%stereo%' AND MyField Like '%speakers%' AND MyField NOT Like '%radio%'

So, your parsing code needs to be something like:

Function ParseConditions(ByVal Cond as String, ByVal CompareFieldName As String) As String
Dim iPos as Integer, sWork as String
Dim sItems() As String
Dim bLastLogical As Integer

Const cAND = 1
Const cOR = 2
Const cNOT = 3

If Trim(Cond) = "" Then
   ParseConditions = ""
   Exit Function
End If

sItems = Split(Cond," ")
iPos = 0
bLastLogical = True
Do While iPos <= UBound(sItems)
   Select Case UCase(sItems(iPos))
   Case "AND"
      If bLastLogical = 0 AND iPos <> UBound(sItems) Then
         sWork = sWork & " AND " & CompareFieldName & " LIKE '%"
         bLastLogical = cAND
      End If
   Case "OR"
      If bLastLogical = 0 AND iPos <> UBound(sItems) Then
         sWork = sWork & " OR " & CompareFieldName & " LIKE '%"
         bLastLogical = cOR
      End If
   Case "NOT"
      If bLastLogical = 0 AND iPos <> UBound(sItems) Then
         sWork = sWork & " AND " & CompareFieldName & " NOT LIKE '%"
         bLastLogical = cNOT
      End If
   Case ""
      ' Ignore - extra space
   Case Else
      If sWork = "" Then sWork = "WHERE " & CompareFieldName & " Like '%"
      sWork = sWork & sItems(iPos) & "%'"
      bLastLogical = 0
   End Select
   iPos = iPos + 1
Loop
If bLastLogical <> 0 Then
   SELECT CASE bLastLogical
   CASE True
      sWork = ""
   CASE cAND
      sWork = Right(sWork, Len(sWork) - (Len(CompareFieldName) + 13)
   CASE cOR
      sWork = Right(sWork, Len(sWork) - (Len(CompareFieldName) + 12)
   CASE cNOT
      sWork = Right(sWork, Len(sWork) - (Len(CompareFieldName) + 17)
   End Select
End If
ParseConditions = sWork
End Function

If I do it that way won't the SQL string end up too long?  or is that not an issue?

The other thing...lets say they do

Radio and 300 not 2000

so I could end up needing to call three separate fields - but I don't know what they are going to search for...this is going to be a major pain in the arse!

so in the above example...

Item = radio

price = 300

year != 2000

Your example won't find the fields that match that right?  Only if they are all in the same field...?

by the way that's why I made this a 300 point question...I knew it was going to a difficult answer!  I have spent the last 2 weeks trying to figure this out (SQL newbie here)

Interesting problem.  Essentially, it sounds like you need to reporduce the access query builder for your users.  Can you tell me a little more about your table - field count etc...?  Do you need to to simulate joins or unions?  Please say no.  It sounds like a neat challange.  One suggestion: for OR conditions on the same field, I normally like to generate a dynamic IN list.

tmpVar = Split(tmpSQL, Chr(32))

For L = LBound(tmpVar) To UBound(tmpVar)
                   
                    If LCase(tmpVar(L)) = "and" Then
                        tmpBuild = tmpBuild & " AND"
                    ElseIf LCase(tmpVar(L)) = "or" Then
                        tmpBuild = tmpBuild & " OR"
                    ElseIf LCase(tmpVar(L)) = "not" Then
                        stbs = True
                        stbs2 = True
                    Else
                        If stbs = False Then
                            tmpBuild = tmpBuild & " [Field*] LIKE '%" & tmpVar(L) & "%'"
                        Else
                            stbs = False
                            tmpBuild = tmpBuild & " AND [Field*] NOT LIKE '%" & tmpVar(L) & "%'"
                        End If
                    End If
       
                Next

' then I replace the [Field*] with my fields names

I think that's pretty much the same as what you gave me...the problem is I can't let the user do a search for 'radio and 2001', or 'radio and sony' as examples.  I have another window that lets people do stuff like that - where I prefill data based on the DB...but a search window is critical!

Falkor, I have 20 fields, one table...however it can be done is fine by me.  As far as I know I don't need joins or unions...  A dynamic IN list sounds like a good idea that I hadn't thought of!

Interesting problem.  Essentially, it sounds like you need to reporduce the access query builder for your users.  Can you tell me a little more about your table - field count etc...?  Do you need to to simulate joins or unions?  Please say no.  It sounds like a neat challange.  One suggestion: for OR conditions on the same field, I normally like to generate a dynamic IN list.

Hmmm... you may need to have multiple condition boxes on your front end, for the fields needed for the compare.  Consider something like:

Item Desc: ________________
Price: __________________
Year: ___________________
etcetera...

Think of how a price matching or item finding web site works (e.g. Travelocity).  You need to specify multiple different fields when defining your query on these sites, and your front end will either need to match that sort of logic, or require entering the field name with each criteria entered (not a good idea).

If you have separate input fields, you can also have different types of parsing.  For example, with your Price field, you could have the user entering a maximum dollars amount, and build your query directly like:

"PRICE <= " & txtPrice.Text

Now, assuming you have multiple text fields (which is what the above parsing logic is set to handle), you would call the function multiple times:

Sub cmdGo_Click ()
Dim sWork as String, sFinal as String
Dim sQuery as String

If txtDescConditions <> "" Then
   sWork = ParseConditions(txtDescConditions, "Description")
   sFinal = sWork
End If

If txtReviewConditions <> "" Then
   sWork = ParseConditions(txtReviewConditions, "Review")
   If sFinal = "" Then
      sFinal = sWork
   Else
      sFinal = sFinal & " AND " & Mid(sWork, 7)
   End If
End If

If txtStoreLocConditions <> "" Then
   sWork = ParseConditions(txtStoreLocConditions, "Store_Location")
   If sFinal = "" Then
      sFinal = sWork
   Else
      sFinal = sFinal & " AND " & Mid(sWork, 7)
   End If
End If

....

Then, handling your numeric fields:

If Val(txtPriceCond) > 0 Then
   If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
   sFinal = sFinal & "Price <= " & Format(Val(txtPriceCond),"##0.00")
End If

....

Finally, build your entire query:

sQuery = "SELECT * From MyTable"
If sFinal <> "" Then sQuery = sQuery & " " & sFinal
Set rs = adoConn.Open sFinal

Hmmm... you may need to have multiple condition boxes on your front end, for the fields needed for the compare.  Consider something like:

Item Desc: ________________
Price: __________________
Year: ___________________
etcetera...

Think of how a price matching or item finding web site works (e.g. Travelocity).  You need to specify multiple different fields when defining your query on these sites, and your front end will either need to match that sort of logic, or require entering the field name with each criteria entered (not a good idea).

If you have separate input fields, you can also have different types of parsing.  For example, with your Price field, you could have the user entering a maximum dollars amount, and build your query directly like:

"PRICE <= " & txtPrice.Text

Now, assuming you have multiple text fields (which is what the above parsing logic is set to handle), you would call the function multiple times:

Sub cmdGo_Click ()
Dim sWork as String, sFinal as String
Dim sQuery as String

If txtDescConditions <> "" Then
   sWork = ParseConditions(txtDescConditions, "Description")
   sFinal = sWork
End If

If txtReviewConditions <> "" Then
   sWork = ParseConditions(txtReviewConditions, "Review")
   If sFinal = "" Then
      sFinal = sWork
   Else
      sFinal = sFinal & " AND " & Mid(sWork, 7)
   End If
End If

If txtStoreLocConditions <> "" Then
   sWork = ParseConditions(txtStoreLocConditions, "Store_Location")
   If sFinal = "" Then
      sFinal = sWork
   Else
      sFinal = sFinal & " AND " & Mid(sWork, 7)
   End If
End If

....

Then, handling your numeric fields:

If Val(txtPriceCond) > 0 Then
   If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
   sFinal = sFinal & "Price <= " & Format(Val(txtPriceCond),"##0.00")
End If

....

Finally, build your entire query:

sQuery = "SELECT * From MyTable"
If sFinal <> "" Then sQuery = sQuery & " " & sFinal
Set rs = adoConn.Open sFinal

One more note:  I should modify the CASE in my original entry to handle the correct entry:

not philadelphia

--------------

Select Case UCase(sItems(iPos))
  Case "AND"
     If bLastLogical = 0 AND iPos <> UBound(sItems) Then
        sWork = sWork & " AND " & CompareFieldName & " LIKE '%"
        bLastLogical = cAND
     End If
  Case "OR"
     If bLastLogical = 0 AND iPos <> UBound(sItems) Then
        sWork = sWork & " OR " & CompareFieldName & " LIKE '%"
        bLastLogical = cOR
     End If
  Case "NOT"
' Change - Handle initial NOT in string
     If bLastLogical = 0 AND iPos <> UBound(sItems) Then
        sWork = sWork & " AND " & CompareFieldName & " NOT LIKE '%"
        bLastLogical = cNOT
     ElseIf bLastLogical < 0 AND iPos <> UBound(sItems) Then
        If sWork = "" Then
           sWork = "WHERE " & CompareFieldName & " NOT LIKE '%"
        Else
           sWork = sWork & " AND " & CompareFieldName & " NOT LIKE '%"
        End If
     End If
  Case ""
     ' Ignore - extra space
  Case Else
     If sWork = "" Then sWork = "WHERE " & CompareFieldName & " Like '%"
     sWork = sWork & sItems(iPos) & "%'"
     bLastLogical = 0
  End Select
  iPos = iPos + 1

On your interface, do you have them typing in their search criteria, or selicting from multi select list boxes, already populated with distinct lists?

I have one window where there are populated list and combo boxes...but this isn't involving that...it's a combo box (just because I have a search history) where they would type something like:

radio and sony not 2000

bhess1...that's a lot of code (which maybe fine) however I am a little confused on one point...are you building the SQl string so it tries to satisfy each thing?

Something like this? (I'm going to use two fields for simplicity)

Search term: Sony and radio not 2000

select * from mytable where myfield1 like %parm(1)% or myfield2 like %parm(1) and myfield1 like %parm(2)% or myfrield2 like %parm(2)% and myfield1 not like %parm(3)% and myfield2 not like %parm(3)%

Am I corrrect?

select * from mytable where myfield1 like %Sony% or myfield2 like %Sony% and (myfield1 like %radio% or myfrield2 like %radio%) and (myfield1 not like %2000% and myfield2 not like %2000%)

something like that?  Sorry this is a bit over my head...I have a bad grasp on anything above simple select/update/insert/delete statements.

No, not at all.  Basically, I am recommending that you input conditions by field.  Certainly, it is possible to check all possible fields for all possible conditions, but this is *NOT* an efficient way to query a database.  Your overhead and query execution time would be horrendous.

Instead, you need to specify your conditions separately for each field that can have conditions, and parse out the conditions into one large WHERE field.

In my last post, at the top, there was a quick example:

Item Desc: ________________
Price: __________________
Year: ___________________
etcetera...

In this example, you might enter:

Item Name: coat
Item Desc: green or blue not pink
Price: 199.95
Year: ___________________

Processing this, your code might look like my example above, with different field names:

Sub cmdGo_Click ()
Dim sWork as String, sFinal as String
Dim sQuery as String

If txtNameConditions <> "" Then
  sWork = ParseConditions(txtNameConditions, "Item_Name")
  sFinal = sWork
End If

If txtDescriptionConditions <> "" Then
  sWork = ParseConditions(txtDescriptionConditions, "Item_Description")
  If sFinal = "" Then
     sFinal = sWork
  Else
     sFinal = sFinal & " AND (" & Mid(sWork, 7) & ")"
  End If
End If

If Val(txtPriceCond) > 0 Then
  If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
  sFinal = sFinal & "Price <= " & Format(Val(txtPriceCond),"##0.00")
End If

If Val(txtYearCond) > 0 Then
  If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
  sFinal = sFinal & "Year = " & Format(Val(txtYearCond), "0000")
End If

sQuery = "SELECT * From MyTable"
If sFinal <> "" Then sQuery = sQuery & " " & sFinal
Set rs = adoConn.Open sFinal

End Sub

Your final query in this case would look like:

SELECT * From MyTable WHERE Item_Name LIKE '%coat%' AND (Item_Desc Like '%green%' OR Item_Desc Like '%blue' AND Item_Desc NOT Like '%pink%') AND Price <= 199.95

This is just a suggestion:

How about concatenating all the columns and use the "like" and "not like" operators in the SQL statement?. If you have number columns, you can convert them to CHAR before concatenating it.

NOTE: The biggest problem with this approach would be that the query would possibly perform a table scan. i.e. it would have slow response time.

mathavra...can you give me an example of what you mean?

Bhess1, I agree that's the best way to do it...however, I need to have a search box that they can enter boolean search phrases in.  El Jefe demands it! haha.  I already have another window that lets the user do that, but El jefe thinks that may turn some people off...down with El jefe!

Here is sample set of code & statements:

1> use tempdb
2> go
1> create table table1
2> (
3> col1 char(10),
4> col2 money,
5> col3 char(5)
6> )
7> go
1> insert into table1 values ("John", 1000, "XXXX")
2> insert into table1 values ("Simba",2000, "Ron")
3> insert into table1 values ("John",2000, "Jane")
4> insert into table1 values ("John",1000,"Jane")
5> go
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1>

1> select
2> col1 + convert(char(15), col2) + col3
3> from table1
4> where
5> col1 + convert(char(15), col2) + col3 like "%John%"  and
6> col1 + convert(char(15), col2) + col3 like "%Jane%"  and
7> col1 + convert(char(15), col2) + col3 not like "%2000%"
8> go

 ------------------------------
 John              1000.00Jane

(1 row affected)

bhess, I agree with your logic :)  I think I am going to copy your post and send email a dirty email saying "REVOLUTION!"  With a DB that is about 1-2 gigs this thing is gonna take 3 days to complete a query...he's gonna have to accept that fact...that'll teach him to not let me get my full-text indexing on

*DOWN WITH EL JEFFE!*

I appreciate everyone elses input, but this is gonna be too damn taxing on the system resources and is going to bog down with multiple people running queries.  So I must go with El Jeffe's I mean Bhess's comments.

Thanks :)