Highlight search results (words) in a report 1

[Webkins]

I have an Access 2003 report which contains the following query in the report record source. This query and report are working just fine. Now I would like to know if it is possible to highlight only the specific search text among all the other text returned with the query.

I am thinking I need to place some VBA code in the report detail section, on format event to highlight the search result word/words ?

SELECT * FROM tbl_data WHERE [tbl_data].[Data_Description] Like "*" & [forms]![frm_search]![SearchString] & "*";

Thank you for you assistance

 

[dhookom]

If the text you want to highlight is the full value of a column/field, you should be able to do this with code in the On Format event of the report section. If you want to do this to a single word or phrase in a larger value, it will be near impossible.

Duane
Hook'D on Access
MS Access MVP

 

[Webkins]

Yes I want to highlight a single word in a phrase or the word contained within another word. For example I want to highlight the word:

light within the phrase: the light was too bright
light within the word: lightning, lightbulb

 

[dhookom]

I expect you might be able to send the report to a different format such as PDF where you can perform your highlighting.

This might be somewhat doable in Access 2003 reports if you are searching fields that won't line wrap. You can use the Print method of the report to display values in different formats.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Here is an example that will print a specific word in a non-wrapped string in red with the remainder of the string in black:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    'Title is the bound control (invisible) in the detail
    '   section of the report that is being searched for
    '   the word "sales"

    Dim strFind As String
    Dim lngTop As Long
    Dim lngLeft As Long
    Dim strLeft As String
    Dim strMid As String
    Dim strRight As String
    
    lngTop = Me.Title.Top
    lngLeft = Me.Title.Left
    strFind = "sales"
    Me.CurrentX = lngLeft
    Me.CurrentY = lngTop
    
    If InStr(Me.Title & "", strFind) = 0 Then
        Me.Print Me.Title & ""
     Else
        Me.Print Left(Me.Title, InStr(Me.Title, strFind) - 1)
        Me.CurrentY = lngTop
        Me.ForeColor = vbRed
        Me.Print Mid(Me.Title, InStr(Me.Title, strFind), Len(strFind))
        Me.CurrentY = lngTop
        Me.ForeColor = vbBlack
        Me.Print Right(Me.Title, Len(Me.Title) + 1 - (InStr(Me.Title, strFind) + Len(strFind)))
    End If
    
    
End Sub

Duane
Hook'D on Access
MS Access MVP