Solved

Comparing Text Strings In Access

Posted on 2002-07-19
9
586 Views
Last Modified: 2012-05-04
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
Comment
Question by:jps9832
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 54

Expert Comment

by:nico5038
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

by:Arthur_Wood
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

by:joekendall
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 44

Accepted Solution

by:
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

by:joekendall
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

by:nico5038
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

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

Dim Cnt1 as long
Dim Position1 a
0
 

Author Comment

by:jps9832
ID: 7165684
Great solution!!! Exactly what I needed!
0
 
LVL 1

Expert Comment

by:janjverb
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Flowing down data to other tables 13 35
Dinamic report to Crosstab query 9 35
Access 2016 - combo box 3 20
Formatting Excel from Access with VBA... 5 21
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question