Highlight search results (1 Viewer)

zcllzzx123

New member
Local time
Today, 04:40
Joined
May 20, 2022
Messages
7
Hi all,

I'm new to access and currently using the 2016 version to build a small database for my office. It has a multi keywords search function to look for results in all fields. I'm trying to highlight keywords in search results and having a difficulty in finding a solution. I've attached my database here, and any hint or help would be appreciated. Thank you!
 

Attachments

  • decisions.zip
    3 MB · Views: 235

Gasman

Enthusiastic Amateur
Local time
Today, 09:40
Joined
Sep 21, 2011
Messages
14,038
Nothing attached?. Unlikely you will be able to until a certain number of posts.
This is to stop spammers.
 

zcllzzx123

New member
Local time
Today, 04:40
Joined
May 20, 2022
Messages
7
Nothing attached?. Unlikely you will be able to until a certain number of posts.
This is to stop spammers.
Sorry I think I attached zipx instead of zip. Now it should appear
 

moke123

AWF VIP
Local time
Today, 05:40
Joined
Jan 11, 2013
Messages
3,849
The first thing you should do is add Option Explicit to your declarations , compile , and fix your spelling errors.
The second is get rid of the attachment fields.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:40
Joined
May 21, 2018
Messages
8,463
Something like?
 

SHANEMAC51

Active member
Local time
Today, 12:40
Joined
Jan 28, 2022
Messages
310
I'm trying to highlight keywords in search results and having a difficulty in finding a solution.
I haven 't seen such an approach to the search yet, it's very original, although not very
- I don't know how to say

how will you search
1 - intervals of dates, amounts
2- in fields with substitutions

I would greatly simplify the code
Code:
Function FrmFilter(param1) As String
If Len("" & param1) = 0 Then FrmFilter = "": Exit Function
Dim s2 As String
s2 = " and (([CaseNumber] like ""*" & param1 & "*"")"
s2 = s2 & " or ([Time] like ""*" & param1 & "*"")"
s2 = s2 & " or ([Issue] like ""*" & param1 & "*"")"
s2 = s2 & " or ([PIPPCoverage] like ""*" & param1 & "*"")"
s2 = s2 & " or ([Jurisdiction] like ""*" & param1 & "*"")"
s2 = s2 & " or ([RelevantLaw] like ""*" & param1 & "*"")"
s2 = s2 & " or ([Injury] like ""*" & param1 & "*"")"
s2 = s2 & " or ([References] like ""*" & param1 & "*"")"
s2 = s2 & " or ([CaseSummary] like ""*" & param1 & "*"")"
s2 = s2 & ")"
FrmFilter = s2
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Search()
Dim T11, T12, T13, T14, T15, T16
Dim task As String

T11 = FrmFilter(Me.SearchBox1.Value)
T12 = FrmFilter(Me.SearchBox2.Value)
T13 = FrmFilter(Me.SearchBox3.Value)
T14 = FrmFilter(Me.SearchBox4.Value)
T15 = FrmFilter(Me.SearchBox5.Value)
T16 = FrmFilter(Me.SearchBox6.Value)

task = T11 & T12 & T13 & T14 & T15 & T16
If Len(task) > 5 Then
task = "select * from RealAICACDecisions where " & Mid(task, 5)
Me.RecordSource = task
End If
End Sub
 
Last edited:

SHANEMAC51

Active member
Local time
Today, 12:40
Joined
Jan 28, 2022
Messages
310
any hint or help would be appreciated
by the way, I tried to find a combination of characters (de) or (ed) - alas, it did not work.
selects only (de) and (ed) , and in different fields
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:40
Joined
May 7, 2009
Messages
19,169
you can only make "highlighting" on a memo (long text).
so i see three fields that are Long text:

' Issue
' RelevantLaw
' CaseSummary

also change the Text format to Richtext (do the same with the corresponding textbox in the form).
 

Attachments

  • decisions.zip
    2.3 MB · Views: 239

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:40
Joined
Feb 19, 2002
Messages
42,970
And finally,
1. Your Dim statements apply ONLY to the last item in the list, not to all of them. Each item must be Dim'd separately.
2. LIKE is a string operator and so it will not necessarily operate as you expect against numbers and dates.
3. Since it makes no sense to use LIKE against dates, you should separate that search completely and use Between to define a range
4. It probably also makes no sense to use LIKE for case number. A combo would probably be best for that.
 

zcllzzx123

New member
Local time
Today, 04:40
Joined
May 20, 2022
Messages
7
you can only make "highlighting" on a memo (long text).
so i see three fields that are Long text:

' Issue
' RelevantLaw
' CaseSummary

also change the Text format to Richtext (do the same with the corresponding textbox in the form).
Thank you very much, and the highlight looks beautiful! However, it seems like the search function can't narrow down records anymore as all records are showing consistently.
 

zcllzzx123

New member
Local time
Today, 04:40
Joined
May 20, 2022
Messages
7
I haven 't seen such an approach to the search yet, it's very original, although not very
- I don't know how to say

how will you search
1 - intervals of dates, amounts
2- in fields with substitutions

I would greatly simplify the code
Code:
Function FrmFilter(param1) As String
If Len("" & param1) = 0 Then FrmFilter = "": Exit Function
Dim s2 As String
s2 = " and (([CaseNumber] like ""*" & param1 & "*"")"
s2 = s2 & " or ([Time] like ""*" & param1 & "*"")"
s2 = s2 & " or ([Issue] like ""*" & param1 & "*"")"
s2 = s2 & " or ([PIPPCoverage] like ""*" & param1 & "*"")"
s2 = s2 & " or ([Jurisdiction] like ""*" & param1 & "*"")"
s2 = s2 & " or ([RelevantLaw] like ""*" & param1 & "*"")"
s2 = s2 & " or ([Injury] like ""*" & param1 & "*"")"
s2 = s2 & " or ([References] like ""*" & param1 & "*"")"
s2 = s2 & " or ([CaseSummary] like ""*" & param1 & "*"")"
s2 = s2 & ")"
FrmFilter = s2
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Search()
Dim T11, T12, T13, T14, T15, T16
Dim task As String

T11 = FrmFilter(Me.SearchBox1.Value)
T12 = FrmFilter(Me.SearchBox2.Value)
T13 = FrmFilter(Me.SearchBox3.Value)
T14 = FrmFilter(Me.SearchBox4.Value)
T15 = FrmFilter(Me.SearchBox5.Value)
T16 = FrmFilter(Me.SearchBox6.Value)

task = T11 & T12 & T13 & T14 & T15 & T16
If Len(task) > 5 Then
task = "select * from RealAICACDecisions where " & Mid(task, 5)
Me.RecordSource = task
End If
End Sub
Thanks for your suggestion! I'm new to access so still learning
 

zcllzzx123

New member
Local time
Today, 04:40
Joined
May 20, 2022
Messages
7
And finally,
1. Your Dim statements apply ONLY to the last item in the list, not to all of them. Each item must be Dim'd separately.
2. LIKE is a string operator and so it will not necessarily operate as you expect against numbers and dates.
3. Since it makes no sense to use LIKE against dates, you should separate that search completely and use Between to define a range
4. It probably also makes no sense to use LIKE for case number. A combo would probably be best for that.
Appreciate that, and good point to the case number and date!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:40
Joined
May 7, 2009
Messages
19,169
Thank you very much, and the highlight looks beautiful! However, it seems like the search function can't narrow down records anymore as all records are showing consistently.
you need to check your Filtering code.
as you can see i am Filtering first the recordset based on your code.
then, on your "filtered" recordset, i am highlighting the key words.
 

moke123

AWF VIP
Local time
Today, 05:40
Joined
Jan 11, 2013
Messages
3,849
you could eliminate a lot of code by only using searchboxes that are populated.
You could do something like this
Code:
Private Function strSearch() As String

    Dim i As Integer, y As Integer
    Dim strOut As String
    Dim strField As Variant

    strField = Split("CaseNumber,Issue,PIPPCoverage,conclusion,Jurisdiction,RelevantLaw,Injury,References,CaseSummary", ",")


    For y = 0 To UBound(strField)

        For i = 1 To 6
            If Nz(Me.Controls("SearchBox" & i), "") <> "" Then

                If strOut <> "" Then
                    strOut = strOut & " Or " & strField(y) & " Like ""*" & Me.Controls("SearchBox" & i) & "*"""
                Else
                    strOut = strField(y) & " Like ""*" & Me.Controls("SearchBox" & i) & "*"""
                End If

            End If
        Next i

    Next y

    strSearch = "select * from RealAICACDecisions where " & strOut
    Debug.Print strSearch

End Function

The output would look like (using 2 searchboxes)
Code:
select * from RealAICACDecisions where CaseNumber Like "*XYZ*" Or CaseNumber Like "*Denied*" Or Issue Like "*XYZ*" Or Issue Like "*Denied*" Or PIPPCoverage Like "*XYZ*" Or PIPPCoverage Like "*Denied*" Or conclusion Like "*XYZ*" Or conclusion Like "*Denied*" Or Jurisdiction Like "*XYZ*" Or Jurisdiction Like "*Denied*" Or RelevantLaw Like "*XYZ*" Or RelevantLaw Like "*Denied*" Or Injury Like "*XYZ*" Or Injury Like "*Denied*" Or References Like "*XYZ*" Or References Like "*Denied*" Or CaseSummary Like "*XYZ*" Or CaseSummary Like "*Denied*"
 
Last edited:

zcllzzx123

New member
Local time
Today, 04:40
Joined
May 20, 2022
Messages
7
you need to check your Filtering code.
as you can see i am Filtering first the recordset based on your code.
then, on your "filtered" recordset, i am highlighting the key words.
the filter was working before but now all records are shown. I'm testing and trying to locate where the issue is
 

zcllzzx123

New member
Local time
Today, 04:40
Joined
May 20, 2022
Messages
7
you could eliminate a lot of code by only using searchboxes that are populated.
You could do something like this
Code:
Private Function strSearch() As String

    Dim i As Integer, y As Integer
    Dim strOut As String
    Dim strField As Variant

    strField = Split("CaseNumber,Issue,PIPPCoverage,conclusion,Jurisdiction,RelevantLaw,Injury,References,CaseSummary", ",")


    For y = 0 To UBound(strField)

        For i = 1 To 6
            If Nz(Me.Controls("SearchBox" & i), "") <> "" Then

                If strOut <> "" Then
                    strOut = strOut & " Or " & strField(y) & " Like ""*" & Me.Controls("SearchBox" & i) & "*"""
                Else
                    strOut = strField(y) & " Like ""*" & Me.Controls("SearchBox" & i) & "*"""
                End If

            End If
        Next i

    Next y

    strSearch = "select * from RealAICACDecisions where " & strOut
    Debug.Print strSearch

End Function

The output would look like (using 2 searchboxes)
Code:
select * from RealAICACDecisions where CaseNumber Like "*XYZ*" Or CaseNumber Like "*Denied*" Or Issue Like "*XYZ*" Or Issue Like "*Denied*" Or PIPPCoverage Like "*XYZ*" Or PIPPCoverage Like "*Denied*" Or conclusion Like "*XYZ*" Or conclusion Like "*Denied*" Or Jurisdiction Like "*XYZ*" Or Jurisdiction Like "*Denied*" Or RelevantLaw Like "*XYZ*" Or RelevantLaw Like "*Denied*" Or Injury Like "*XYZ*" Or Injury Like "*Denied*" Or References Like "*XYZ*" Or References Like "*Denied*" Or CaseSummary Like "*XYZ*" Or CaseSummary Like "*Denied*"
Will give a try, thank you very much!
 

Users who are viewing this thread

Top Bottom