How to compare 2 similar strings letter by letter and highlight the differences?

Question

We have 2 databases that should have matching tables. I have an (In-Production) report that compares these fields and displays them to the user in an MS-Access form (continuous form style) for correction.

This is all well and good except it can be difficult to find the differences. How can I format these fields to bold/italicize/color the differences?

"The lazy dog jumped over a brown fox."
"The lazy dog jumped over the brown fox."

(It's easier to see the differences between 2 similiar text fields once they are highlighted in some way)

"The lazy dog jumped over a brown fox."
"The lazy dog jumped over the brown fox. "

Since we're talking about a form in MS Access, I don't have high hopes. But I know I'm not the first person to have this problem. Suggestions?

---

Edit

I've gone with Remou's solution. It's not my ideal solution, but it is "good enough", especially since I don't have any rich text options. In the query that builds the source table, I used space() to add trailing spaces to make both fields of equal length. Then I added this code to the Click event of both fields (with TextA and TextB reversed for the other field):

    Dim i As Integer
    For i = 1 To Len(Me.TextA.Text)
        If Right(Left(Me.TextA.Value, i), 1) <> _
        Right(Left(Me.TextB.Value, i), 1) Then
            Me.TextA.SelStart = i - 1
            Me.TextA.SelLength = Len(Me.TextA.Text)
            Exit For
        End If
    Next i

The result is that when you click on each field, the first "differing letter" to the end of the string is selected. I was able to experiment, code, and text this quickly, so I went with it. But I'll be revisiting this idea sooner or later since this concept would be useful in several projects.

Answer 1

You could set selstart and sellength, this will select a part of the textbox. There are some dangers, in that the user may lean on a key and clear the selection.

Answer 2

You could look into using a rich text box control. That would allow you to change fonts, bold, italicize, ect.

There are a few issues. If using Access 2003 or XP, then you should avoid the Rich TextBox Control 6.0 http://support.microsoft.com/kb/838010 It has nasty security issues, and Microsoft says it should be avoided at all costs.

If you are using 2003 or XP you can look into this rich text control http://www.lebans.com/richtext.htm Its older but should work with 2003 and XP. I've never used it personally, so I'm not sure how well it works.

Answer 3

Rich text is supported and built into ms-access for the last two version. So, you can build a screen like:

The 3rd column in the above is simply bound to a function that displays the difference between the two collums. The above is a actual screen shot running with the following code for the 3rd collum/function.

Here the code:

bolSame = True
i1 = 1: i2 = 1
   For i = 1 To Len(c2t)
      c1 = Mid(c1t, i1, 1)
      c2 = Mid(c2t, i2, 1)
      s = c2
      If c1 = c2 Then
         If bolSame = False Then
            bolSame = True
            s = "</strong></font>" & s
         End If
         i1 = i1 + 1: i2 = i2 + 1
      Else
         If bolSame = True Then
            bolSame = False
            s = "<font color=red><strong>" & s
         End If
         i1 = i1 + 1: i2 = i2 + 1
      End If
      strResult = strResult & s
   Next

If bolSame = False Then
   strResult = strResult & "</strong></font>"
End If
MyCompare = strResult

I really don't think the problem here is producing a string, I the REAL hard problem is when the strings are different lengths. It is FAR from a trivial coding exercise to display differences in two strings. You can certainly display from where they are different on wards, but highlighting each difference is a difficult coding problem.

Answer 4

Access 2007 introduced a vastly improved rich textbox that understands HTML and if I felt it was important to do something more than @Remou's suggestion of selecting the differing text, I'd delve into that.

I'd be much more concerned about how to write the code that figures out what's different between the two examples. That seems to me like a much, much harder problem.