Same String Appears Twice in Text Field

[CPGDeveloper]

Is there a way I can identify records where the same string appears twice in a particular field?

For example, if I have the following:

ID, Field1
1, PO BOX 2
2, PO BOX 2 PO
3, APT 1
4, APT APT 2

How would I pull out rows 2 and 4 in a query?

Thanks!

 

[james_lankford]

make a module
put this code in it

'*****************************************
Function has_dup_strings(v As Variant) As Boolean
    
    has_dup_strings = False
    
    Dim counter As Long
    Dim counter2 As Long
    Dim num_elements As Long
    Dim arry As Variant
    Dim string_to_test As Variant
    
    arry = Split(v, " ")
    num_elements = UBound(arry)
    
    For counter = 0 To num_elements
        string_to_test = arry(counter)
        For counter2 = counter + 1 To num_elements
            If string_to_test = arry(counter2) Then
                has_dup_strings = True
                Exit Function
            End If
        Next
    Next
    
End Function
'*****************************************

make a query and use the function in the query

SELECT 
  Table1.id, 
  Table1.t, 
  has_dup_strings ( [t] ) AS Expr1
FROM 
  Table1;

not thoroughly tested, but seems to work

 

[CPGDeveloper]

That's Brilliant! Thanks!