Highlight search results

[zcllzzx123]

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!

 

[Gasman]

Nothing attached?. Unlikely you will be able to until a certain number of posts.
This is to stop spammers.

 

[zcllzzx123]

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]

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]

Something like?

Formatting Keyword in Text or Memo field.

Is there any way of conditional formatting a Keyword in a Text or Memo field? I tried to do a search on this but came up with nothing. What I am trying to do is in a report, if I specifically set a temporary variable to a keyword and the keyword appears in a report's Text or Memo field, it...

www.access-programmers.co.uk

 

[SHANEMAC51]

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

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

 

[SHANEMAC51]

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]

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).

 

[Pat Hartman]

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]

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]

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

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]

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]

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]

you could eliminate a lot of code by only using searchboxes that are populated.
You could do something like this

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)

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*"

 

[zcllzzx123]

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]

you could eliminate a lot of code by only using searchboxes that are populated.
You could do something like this

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)

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!

 

[moke123]

Will give a try, thank you very much!

heres your db using the above search routine