jps9832
asked on
Comparing Text Strings In Access
Is there an easy way to compare two fields so that I can tell if at least any five characters together in one field match the same five characters in another field?
Nico, your answer won't work in general, as INSTR will return a HIT if those 5 characters are found ANYWHERE in the second string, but the question asked for THE SAME FIVE CHARACTERS in the second string. You would need to test that the value returned by INSTR, if NOT 0, was equal to the starting position of the 5 Character string from the First field.
Arthur Wood
Arthur Wood
Here is a code solution that will work for what you need:
'''''''''''''''''''''''''' '''''''''' '''''''''' '''''''''' '''''''''' ''''''''''
'Use the ReturnValues sub to fill an Array with all of the 5 consecutive
'letters possibilities in String1 that are in String2.
'
'Created by Joe Kendall 7/19/2002
'''''''''''''''''''''''''' '''''''''' '''''''''' '''''''''' '''''''''' ''''''''''
Private m_sArray() As String
Private m_nCount As Long
Public Sub ReturnValues(String1 As String, String2 As String)
'''''''''''''''''''''''''' '''''''''' '''''''''' '''''''''' '
'Usage: Call ReturnValues("ABCDEFGHI", "ZABABCDEFGHIZ")
'''''''''''''''''''''''''' '''''''''' '''''''''' '''''''''' '
Dim nLen As Long
Dim nPos As Long
If Len(String1) < 5 Then Exit Sub
If Len(String2) < 5 Then Exit Sub
m_nCount = 0
ReDim m_sArray(0)
nLen = Len(String1)
Do While (nPos + 5) <= nLen
nPos = nPos + 1
Call CompareString(String1, String2, nPos)
Loop
Call PrintValues
End Sub
Private Sub CompareString(String1 As String, String2 As String, Position As Long)
If InStr(1, String2, MID$(String1, Position, 5)) > 0 Then
m_nCount = m_nCount + 1
ReDim Preserve m_sArray(m_nCount)
m_sArray(m_nCount) = MID$(String1, Position, 5)
End If
End Sub
Private Sub PrintValues()
Dim nCount As Long
Dim nCounter As Long
nCount = UBound(m_sArray())
For nCounter = 0 To nCount
Debug.Print m_sArray(nCounter)
Next
End Sub
Let me know if you have any questions.
Thanks!
Joe
''''''''''''''''''''''''''
'Use the ReturnValues sub to fill an Array with all of the 5 consecutive
'letters possibilities in String1 that are in String2.
'
'Created by Joe Kendall 7/19/2002
''''''''''''''''''''''''''
Private m_sArray() As String
Private m_nCount As Long
Public Sub ReturnValues(String1 As String, String2 As String)
''''''''''''''''''''''''''
'Usage: Call ReturnValues("ABCDEFGHI", "ZABABCDEFGHIZ")
''''''''''''''''''''''''''
Dim nLen As Long
Dim nPos As Long
If Len(String1) < 5 Then Exit Sub
If Len(String2) < 5 Then Exit Sub
m_nCount = 0
ReDim m_sArray(0)
nLen = Len(String1)
Do While (nPos + 5) <= nLen
nPos = nPos + 1
Call CompareString(String1, String2, nPos)
Loop
Call PrintValues
End Sub
Private Sub CompareString(String1 As String, String2 As String, Position As Long)
If InStr(1, String2, MID$(String1, Position, 5)) > 0 Then
m_nCount = m_nCount + 1
ReDim Preserve m_sArray(m_nCount)
m_sArray(m_nCount) = MID$(String1, Position, 5)
End If
End Sub
Private Sub PrintValues()
Dim nCount As Long
Dim nCounter As Long
nCount = UBound(m_sArray())
For nCounter = 0 To nCount
Debug.Print m_sArray(nCounter)
Next
End Sub
Let me know if you have any questions.
Thanks!
Joe
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can also make a minor change in mine and it will compare case sensitive letters too. So that ABCDE will not equal abcde.
Thanks!
Joe
Thanks!
Joe
I guess you can be right Arthur, it's just how you read the Q.
But INSTR can be used, as it returns the first position and comparing that with the start of the "field1 5 character string" can solve that ;-)
Lets hear what jps9832 needs:
12345a matches a12345
and/or
12345a matches 12345b
Nic;o)
But INSTR can be used, as it returns the first position and comparing that with the start of the "field1 5 character string" can solve that ;-)
Lets hear what jps9832 needs:
12345a matches a12345
and/or
12345a matches 12345b
Nic;o)
Public function Match5(One as String, Two as String) as boolean
Dim Cnt1 as long
Dim Position1 a
Dim Cnt1 as long
Dim Position1 a
ASKER
Great solution!!! Exactly what I needed!
A more 'general' solution with NumberOfChar instead of 5:
Public Function MatchX(One As String, Two As String, NumberOfChar As Long) As Boolean
Dim Cnt1 As Long
Dim TestStr As String
If Len(One) < NumberOfChar Or Len(Two) < NumberOfChar Then
MatchX = False
Exit Function
End If
For Cnt1 = 1 To Len(One) - NumberOfChar + 1
TestStr = Mid(One, Cnt1, NumberOfChar)
If InStr(Two, TestStr) > 0 Then
MatchX = True
Exit Function
End If
Next Cnt1
MatchX = False
End Function
jjv
Public Function MatchX(One As String, Two As String, NumberOfChar As Long) As Boolean
Dim Cnt1 As Long
Dim TestStr As String
If Len(One) < NumberOfChar Or Len(Two) < NumberOfChar Then
MatchX = False
Exit Function
End If
For Cnt1 = 1 To Len(One) - NumberOfChar + 1
TestStr = Mid(One, Cnt1, NumberOfChar)
If InStr(Two, TestStr) > 0 Then
MatchX = True
Exit Function
End If
Next Cnt1
MatchX = False
End Function
jjv
Niv;o)