Super Search returning records faulty

[splats]

Hello

I have a complicated search that is not returning the correct records that I would like.

#1 I have an unbound textbox [txtSearchFor] that checks many fields to see if the data is in there. There is an option group [fraUsing] here as well that determines the combination of the words that are input in the textbox being as follows, (Any of the words entered, All or Exact Phrase)

#2 As well, I have 3 other fields that are unbound as well that return records that are not able to be looked up within the primary search because they are lookup fields. [ComboName], [ComboFrom], [ComboTo].

I would like the combined searches to work together so that if there is a word within the primary search, that it filters the secondary search and vice versa.

Below is my code, presently the combined searches both work but do not work interactively. e.g. records are returned for both searches but are not restricted to the records that meet both criteria.

Thank you

Private Sub cmdSearch_Click()

Dim arrSearch() As String
Dim i As Integer
Dim strWhere As String
Dim strTemp As String
Dim arrStreets() As String
Dim strStreets As String

Dim lngRcnt As Long
Dim ValName As Long
Dim ValFrom As Long
Dim ValTo As Long
Dim ISTHERETEXT As Boolean

Dim temp1 As String
Dim temp2 As String
Dim temp3 As String
Dim Bool As String

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim strFilter As String

strFilter = ""

'Values from the Combo Box
'Needed to add a correction factor of one
ValName = Me.ComboName.ListIndex + 1
ValFrom = Me.ComboFrom.ListIndex + 1
ValTo = Me.ComboTo.ListIndex + 1

' If no option is selected in one of the StreeName, StreetFrom, StreetTo
' drop down menus, the value is changed to 999999 in order to make the
' search not match any fields.
If ValName = 0 Then
ValName = 999999
End If
If ValFrom = 0 Then
ValFrom = 999999
End If
If ValTo = 0 Then
ValTo = 999999
End If

'Search based on text entry and drop down menus
If txtSearchFor <> "" Then
If fraUsing = 1 Then ' any
arrSearch() = Split(txtSearchFor, " ", -1, 1)
For i = 0 To UBound(arrSearch)
If i = 0 Then
strWhere = strWhere & " ("
Else
strWhere = strWhere & " AND "
End If

strtemp1 = Trim(arrSearch(i))
strWhere = strWhere & "([PlanNo] LIKE '*" & strtemp1 & "*' )" & " OR ([PlanDescription] LIKE '*" & strtemp1 & "*')" & " OR ([DeveloperName] LIKE '*" & strtemp1 & "*')" & " OR ([Full Address] LIKE '*" & strtemp1 & "*')" & " OR ([SheetNo] LIKE '*" & strtemp1 & "*')" & " OR (ConsultantName LIKE '*" & strtemp1 & "*')" & " OR (ContractNo LIKE '*" & strtemp1 & "*')" & " OR (ConsultantPlanNo LIKE '*" & strtemp1 & "*')" & " OR (SheetDescription LIKE '*" & strtemp1 & "*')" & " OR (TypeID LIKE '*" & strtemp1 & "*')" & " OR " & " (StreetNameID = " & ValName & ") AND (StreetFromID = " & ValFrom & ") AND (StreetToID = " & ValTo & ")"
Next

ElseIf fraUsing = 2 Then ' all
arrSearch = Split(txtSearchFor, " ", -1, 1)

Set Db = CurrentDb
Set rs = Db.OpenRecordset("SELECT StreetNameId, StreetFromID, StreetToID FROM tblsheetstreetdetails WHERE StreetNameID = " & ValName & " AND StreetFromID = " & ValFrom & " AND StreetToID = " & ValTo & " ")
rs.Filter = "(StreetNameID = " & ValName & " AND StreetFromID = " & ValFrom & " AND StreetToID = " & ValTo & ")"
strFilter = rs.Filter

For i = 0 To UBound(arrSearch)
If i = 0 Then
strWhere = strWhere & " ("
Else
strWhere = strWhere & " AND "
End If

strtemp1 = Trim(arrSearch(i))
strWhere = strWhere & "(([PlanNo] LIKE '*" & strtemp1 & "*' )" & " OR ([PlanDescription] LIKE '*" & strtemp1 & "*')" & " OR ([DeveloperName] LIKE '*" & strtemp1 & "*')" & " OR ([Full Address] LIKE '*" & strtemp1 & "*')" & " OR ([SheetNo] LIKE '*" & strtemp1 & "*')" & " OR (ConsultantName LIKE '*" & strtemp1 & "*')" & " OR (ContractNo LIKE '*" & strtemp1 & "*')" & " OR (ConsultantPlanNo LIKE '*" & strtemp1 & "*')" & " OR (SheetDescription LIKE '*" & strtemp1 & "*')" & " OR (TypeID LIKE '*" & strtemp1 & "*'))"
Next

Else 'exact
Set Db = CurrentDb
Set rs = Db.OpenRecordset("SELECT StreetNameId, StreetFromID, StreetToID FROM tblsheetstreetdetails WHERE StreetNameID = " & ValName & " AND StreetFromID = " & ValFrom & " AND StreetToID = " & ValTo & " ")

If (fraUsing = 3 And ValName <> 999999 And ValFrom <> 999999 And ValTo = 999999) Then
rs.Filter = "(StreetNameID = " & ValName & " AND StreetFromID = " & ValFrom & " OR StreetToID = " & ValTo & ")"
ElseIf (fraUsing = 3 And ValName <> 999999 And ValFrom = 999999 And ValTo <> 999999) Then
rs.Filter = "(StreetNameID = " & ValName & " AND StreetFromID = " & ValTo & " OR StreetToID = " & ValFrom & ")"
ElseIf (fraUsing = 3 And ValName = 999999 And ValFrom <> 999999 And ValTo <> 999999) Then
rs.Filter = "(StreetNameID = " & ValName & " OR StreetFromID = " & ValFrom & " AND StreetToID = " & ValTo & ")"
'case # = 1
ElseIf (fraUsing = 3 And ValName <> 999999 And ValFrom = 999999 And ValTo = 999999) Then
rs.Filter = "(StreetNameID = " & ValName & " OR StreetFromID = " & ValFrom & " OR StreetToID = " & ValTo & ")"
ElseIf (fraUsing = 3 And ValName = 999999 And ValFrom <> 999999 And ValTo = 999999) Then
rs.Filter = "(StreetNameID = " & ValName & " OR StreetFromID = " & ValFrom & " OR StreetToID = " & ValTo & ")"
ElseIf (fraUsing = 3 And ValName = 999999 And ValFrom = 999999 And ValTo <> 999999) Then
rs.Filter = "(StreetNameID = " & ValName & " OR StreetFromID = " & ValFrom & " OR StreetToID = " & ValTo & ")"
'case # = 3
ElseIf (fraUsing = 3 And ValName <> 999999 And ValFrom <> 999999 And ValTo <> 999999) Then
rs.Filter = "(StreetNameID = " & ValName & " AND StreetFromID = " & ValFrom & " AND StreetToID = " & ValTo & ")"
End If

strFilter = rs.Filter
strWhere = strWhere & " ("
strWhere = strWhere & "([PlanNo] LIKE '*" & Trim(txtSearchFor) & "*' )" & " OR ([PlanDescription] LIKE '*" & Trim(txtSearchFor) & "*')" & " OR ([DeveloperName] LIKE '*" & strtemp1 & "*')" & " OR ([Full Address] LIKE '*" & Trim(txtSearchFor) & "*')" & " OR ([SheetNo] LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (ConsultantName LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (ContractNo LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (ConsultantPlanNo LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (SheetDescription LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (TypeID LIKE '*" & Trim(txtSearchFor) & "*')"
End If

strWhere = strWhere & ") "

If (fraUsing = 1) Then
strWhere = strWhere
ElseIf ((fraUsing = 2 Or fraUsing = 3) And (ValName = 999999 And ValFrom = 999999 And ValTo = 999999)) Then
strWhere = strWhere
Else
strWhere = strWhere & " AND " & strFilter
End If

[frm-subdata1].Form.Filter = strWhere
[frm-subdata1].Form.FilterOn = True
Else

' No text in search box, Search is only dependant on the drop down menus
strTemp = ValName & " " & ValFrom & " " & ValTo

arrStreets = Split(strTemp, " ", -1, 1)

'case # = 2
If (fraUsing = 3 And ValName <> 999999 And ValFrom <> 999999 And ValTo = 999999) Then
strStreets = "(StreetNameID = " & ValName & " AND StreetFromID = " & ValFrom & " OR StreetToID = " & ValTo & ")"
ElseIf (fraUsing = 3 And ValName <> 999999 And ValFrom = 999999 And ValTo <> 999999) Then
strStreets = "(StreetNameID = " & ValName & " AND StreetFromID = " & ValTo & " OR StreetToID = " & ValFrom & ")"
ElseIf (fraUsing = 3 And ValName = 999999 And ValFrom <> 999999 And ValTo <> 999999) Then
strStreets = "(StreetNameID = " & ValName & " OR StreetFromID = " & ValFrom & " AND StreetToID = " & ValTo & ")"
'case # = 1
ElseIf (fraUsing = 3 And ValName <> 999999 And ValFrom = 999999 And ValTo = 999999) Then
strStreets = "(StreetNameID = " & ValName & " OR StreetFromID = " & ValFrom & " OR StreetToID = " & ValTo & ")"
ElseIf (fraUsing = 3 And ValName = 999999 And ValFrom <> 999999 And ValTo = 999999) Then
strStreets = "(StreetNameID = " & ValName & " OR StreetFromID = " & ValFrom & " OR StreetToID = " & ValTo & ")"
ElseIf (fraUsing = 3 And ValName = 999999 And ValFrom = 999999 And ValTo <> 999999) Then
strStreets = "(StreetNameID = " & ValName & " OR StreetFromID = " & ValFrom & " OR StreetToID = " & ValTo & ")"
'case # = 3
ElseIf (fraUsing = 3 And ValName <> 999999 And ValFrom <> 999999 And ValTo <> 999999) Then
strStreets = "(StreetNameID = " & ValName & " AND StreetFromID = " & ValFrom & " AND StreetToID = " & ValTo & ")"
End If

If (fraUsing = 2 Or fraUsing = 1) Then
For j = 0 To UBound(arrStreets)
If j = 0 Then
strStreets = strStreets & " ("
Else
strStreets = strStreets & " OR "
End If

'Filter out the streets chosen from the drop-down boxes
'Can filter on one or all of the drop-down boxes so need to check all of them

'**********************************************
If j = 0 Then

'transfer into a variable because form.filter will not handle an array
temp1 = arrStreets(j)
strStreets = strStreets & " (StreetNameID = " & temp1 & ")"

ElseIf j = 1 Then
temp2 = arrStreets(j)
strStreets = strStreets & " (StreetFromID = " & temp2 & ")"

ElseIf j = 2 Then
temp3 = arrStreets(j)
strStreets = strStreets & " (StreetToID = " & temp3 & ")"

strStreets = strStreets & " )"
End If
Next
'**********************************************
End If

[frm-subdata1].Form.Filter = strStreets
[frm-subdata1].Form.FilterOn = True

End If

' If nothing is entered in the txtbox or any of the dropdown menus then
' All the records will show.
If txtSearchFor <> "" Then
ISTHERETEXT = True
End If
If (ValName = 999999) Then
If (ValFrom = 999999) Then
If (ValTo = 999999) Then
If Not ISTHERETEXT Then
[frm-subdata1].Form.FilterOn = False
End If
End If
End If
End If
End Sub

 

[kjv1611]

I have a complicated search that is not returning the correct records that I would like.

[ol]
[li]Are you getting any error messages?[/li]
[li]What is this search returning?[/li]
[li]What are you wanting it to return?[/li]
[/ol]

--

"If to err is human, then I must be some kind of human!" -Me

 

[splats]

Hello kjv1611

Thank you for your input.

I am not getting any errors.
The search is returning records for each #1 and #2 which is fine, however, I would like the 2 searches to filter each other. e.g Search #1 returns A, B and C. Search #2 Returns B, D, F.

Ideally instead of getting A, B, C, D and F for a result, I would like to get only the answer B.

Tina

 

[kjv1611]

So, is it not possible to just run a separate query to compare the two results against each other? Or either in your code, have 3 separate sections:

1. Query based on #1
2. Query based on #2
3. Query based on #3

You might could then put those 3 into one public function, or either 3 separate functions, and call them according to what control is updated on your form.

I've not taken the time to delve into all the detail of the code, I'm just going on the idea/thought process at this point.

Would that work in your situation?

--

"If to err is human, then I must be some kind of human!" -Me

 

[splats]

Hello Kjv1611

I do not think that the queries would work as there are several different levels to this search.

There is an unbound text box that looks up data in many fields. [txtSearchFor]
There are 3 options for the combination of data that is found, All Words, Any Words and Exact Phrase.
There are 3 fields that all have data originating from the same drop down list of streets but are linked to different fields, they are: (StreetNameID, StreetToID, StreetFromID).

I think that there is something wrong with when[txtSearchFor] has a phrase in it and the user chooses to search the exact phrase. For some reason the records that it returns are not what should be returned. I think that the code as below is faulty.

strWhere = strWhere & "([PlanNo] LIKE '*" & Trim(txtSearchFor) & "*' )" & " OR ([PlanDescription] LIKE '*" & Trim(txtSearchFor) & "*')" & " OR ([DeveloperName] LIKE '*" & strtemp1 & "*')" & " OR ([Full Address] LIKE '*" & Trim(txtSearchFor) & "*')" & " OR ([SheetNo] LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (ConsultantName LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (ContractNo LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (ConsultantPlanNo LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (SheetDescription LIKE '*" & Trim(txtSearchFor) & "*')" & " OR (TypeID LIKE '*" & Trim(txtSearchFor) & "*')"

this is my hunch... Any other ideas out there?

Thank you

tina

 

[PHV]

As a general guideline, double check the parentheses when mixing OR and AND operators.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886