Solved

# Comparing Text Strings In Access

Posted on 2002-07-19
592 Views
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?
0
Question by:jps9832
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2
• +2

LVL 54

Expert Comment

ID: 7165445
No, you'll need a function that's taking all possible 5 characters from the first field (assuming it's at least 5 characters or more in length) and uses an INSTR to detect a "hit".

Niv;o)
0

LVL 44

Expert Comment

ID: 7165564
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
0

LVL 11

Expert Comment

ID: 7165571
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
0

LVL 44

Accepted Solution

Arthur_Wood earned 100 total points
ID: 7165587
try something like this:

Public Function Match5(String1 as String, String2 as String) as Boolean
Dim iPos as Integer
Dim SubString as String
Match5 = False
If Len(String1) >= 5 then
If Len(string2) >= 5 then
for iPos = 1 to len(String1) - 4
Substring = Mid\$(String1,iPos,iPos+4))
If Instr(iPos,String2,SubString) = iPos then
Match5 = True
Exit For
end if
Next
End if
End if
End Function

Arthur Wood
0

LVL 11

Expert Comment

ID: 7165604
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
0

LVL 54

Expert Comment

ID: 7165655
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)
0

LVL 1

Expert Comment

ID: 7165677
Public function Match5(One as String, Two as String) as boolean

Dim Cnt1 as long
Dim Position1 a
0

Author Comment

ID: 7165684
Great solution!!! Exactly what I needed!
0

LVL 1

Expert Comment

ID: 7165696
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
###### Suggested Courses
Course of the Month5 days, 18 hours left to enroll