search priority

  • Thread starter Thread starter miss031
  • Start date Start date
M

miss031

I made a search box at the top of my form, so users can search by name, phone
number or bidder number, using the code below. If the user types in "71" it
will bring up bidder 71, 710, 711, etc., as well as anyone who has 71 in
their phone number. What I would like is to prioritize the search results,
bringing exact matches to the bidder number to the top of the list. Is this
something I would do in the code, or the query?


Dim StrWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True

Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 5 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
StrWhere = StrWhere & "([lastname] Like ""*" & strWord & _
"*"") OR ([contactcompany] Like ""*" & strWord &
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like ""*" &
strWord & "*"") OR "
End If
Next
lngLen = Len(StrWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(StrWhere, lngLen)
Me.FilterOn = True
Debug.Print StrWhere
Else
Me.FilterOn = False
End If
End If
End If
 
Hi miss031,

in my experience, searching on a number field is quickest and easiest with a
combo.
The coding for this is easier also. As the user types 7, then 1 into the
combo, the combo quickly finds the exact match and avoids all the nusiance
records with a 7 that also appear if you use the code you posted.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
I though of that, but I kinda like the wildcard feature of all of them
showing up, I just wondered if I could somehow sort the results so the exact
matches show up at the top of the list. Unless....

Maybe I could give the option of a wildcard feature? I saw on a website,
it was set up so if you put a % sign in your search term, it would act as a
wildcard, otherwise it would match only exactly. Is that possible with my
code?

Jeanette Cunningham said:
Hi miss031,

in my experience, searching on a number field is quickest and easiest with a
combo.
The coding for this is easier also. As the user types 7, then 1 into the
combo, the combo quickly finds the exact match and avoids all the nusiance
records with a 7 that also appear if you use the code you posted.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


miss031 said:
I made a search box at the top of my form, so users can search by name,
phone
number or bidder number, using the code below. If the user types in "71"
it
will bring up bidder 71, 710, 711, etc., as well as anyone who has 71 in
their phone number. What I would like is to prioritize the search results,
bringing exact matches to the bidder number to the top of the list. Is
this
something I would do in the code, or the query?


Dim StrWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True

Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 5 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
StrWhere = StrWhere & "([lastname] Like ""*" & strWord
& _
"*"") OR ([contactcompany] Like ""*" & strWord &
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like ""*"
&
strWord & "*"") OR "
End If
Next
lngLen = Len(StrWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(StrWhere, lngLen)
Me.FilterOn = True
Debug.Print StrWhere
Else
Me.FilterOn = False
End If
End If
End If
Click to expand...
Click to expand...
 
I don't know any way to sort the numbers so that exact matches go to the
top.
Either you look for the exact number that the user typed in or you use the
wild card in the code you posted and get 71, 710, 711, etc., as well as
anyone who has 71 in their phone number.

To get an exact match if bidder is a number you would use
StrWhere = StrWhere & "([bidder] = " & strWord

if bidder is text you would use
StrWhere = StrWhere & "([bidder] = """ & strWord & ""

With either of the two above samples, remove the remaining 'OR' clauses from
strWhere when you are looking for the exact bidder.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


miss031 said:
I though of that, but I kinda like the wildcard feature of all of them
showing up, I just wondered if I could somehow sort the results so the
exact
matches show up at the top of the list. Unless....

Maybe I could give the option of a wildcard feature? I saw on a website,
it was set up so if you put a % sign in your search term, it would act as
a
wildcard, otherwise it would match only exactly. Is that possible with my
code?

Jeanette Cunningham said:
Hi miss031,

in my experience, searching on a number field is quickest and easiest
with a
combo.
The coding for this is easier also. As the user types 7, then 1 into the
combo, the combo quickly finds the exact match and avoids all the
nusiance
records with a 7 that also appear if you use the code you posted.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


miss031 said:
I made a search box at the top of my form, so users can search by name,
phone
number or bidder number, using the code below. If the user types in
"71"
it
will bring up bidder 71, 710, 711, etc., as well as anyone who has 71
in
their phone number. What I would like is to prioritize the search
results,
bringing exact matches to the bidder number to the top of the list. Is
this
something I would do in the code, or the query?


Dim StrWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True

Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 5 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
StrWhere = StrWhere & "([lastname] Like ""*" &
strWord
& _
"*"") OR ([contactcompany] Like ""*" & strWord &
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like
""*"
&
strWord & "*"") OR "
End If
Next
lngLen = Len(StrWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(StrWhere, lngLen)
Me.FilterOn = True
Debug.Print StrWhere
Else
Me.FilterOn = False
End If
End If
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top