comparing text within a string to keep only unique values

[sanzoo]

Hello guys,

I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:

initial field:
xxx;yyy;ppp;yyy

targeted result:
xxx;yyy;ppp

Any thoughts how this could be achieved ?
Thanks

 

[pr2-eugin]

How about something like this..

Public Function getUniqueString(txtToSearch As String) As String
[COLOR=Green]'-----------------------------------------------------------------------
'   A function that will take in the string to search for and
'   returns only non repetive words contained in the inital String.
'
'   Input : String to look for, RegEx String
'   Output: The string after getting the unique values.
'
'   Example:
'       ? getUniqueString("xxx;yyy;yyy;zzz;ppp;xxx")
'         xxx;yyy;zzz;ppp
'-------------------------------------------------------------------------[/COLOR]
    Dim initStr() As String, endStr As String
    Dim i As Integer, j As Integer, repInt As Integer
    
    initStr = Split(txtToSearch, ";")
    repInt = 0
    
    For i = 0 To UBound(initStr)
        For j = 0 To UBound(initStr)
            If initStr(i) = initStr(j) Then repInt = (repInt + 1) Mod 2
        Next
        If repInt < 2 And InStr(endStr, initStr(i)) = 0 Then endStr = endStr & ";" & initStr(i)
        repInt = 0
    Next
    
    getUniqueString = Right(endStr, Len(endStr) - 1)
End Function

 

[jdraw]

Suggest your research the Split() function

OOoops: I see Paul had the same idea.

 

[pr2-eugin]

Hello jdraw, when you second my suggestion it feels like I am getting better at Access and VBA.. Thank you..

 

[sanzoo]

Thank you Paul and Jdraw for the code and suggestion it looks exactly what I need however when I try to use Paul`s code it shows following error message : "Run-time error 5 :Invalid procedure call or argument and highlighting the last line of the code " getUniqueString = Right(endStr, Len(endStr) - 1)" in debug mode. Any ide what is causing the problem ?

 

[sanzoo]

OK I got it problem was with blank ("") fields once covered these it worked like a charm. Thank you very much indeed.