Is there a way to Delete repeating words from a string of many feilds

[Yarbz]

Greetings all,
I have a report that my docs want. but it takes from many feilds to create each line. Some of the med listed have repeat words in them. Is there a way to filter or remove the repeated words?
I'm working in Access 2003.
The string of meds goes together like this:

[Med Name]&" "&[Dose Type]&" "&[Dosage]&" "&[Dose Unit]&" "&[Route]&" "&[Frequency]&" for "&[Reason Txt]

This would translate to:
Lamotrigine (Lamictal) tab, 100 mg po daily-am for breakthrough irritability, obtrusiveness, restlessness optimizing dose according to blood levels
Or:
Clozapine (Fazaclo) Oral Disintegrating Tab tab, 100 mg po bid for psychosis suspected medication nonadherence based on
incongruent serum clozapine levels

What is needed is the double or repeated words (like tab tab above) that are not always in there, be reduced to only once.
I can do it in word, but how do you do it in Access.
Thanks
Yarbz

 

[MajP]

Give this a try.
There are some simpliar methods but most code I seen would not handle your example.
tab tab,
or .... someword someword.
becaue "tab" does not equal "tab,"

I have seen some codes that uses instr but I could see that causing some problems with things like
... cap capability...

Public Function removeDups(strMemo As Variant) As Variant
  Dim aWords() As String
  Dim strWord As String
  Dim strNextWord As String
  Dim i As Integer
  Dim itm As Variant
If Not IsNull(strMemo) Then
  'remove all double spaces in memo
  strMemo = Replace(strMemo, "  ", " ")
  aWords = Split(strMemo, " ")
  For i = 0 To UBound(aWords) - 1
    strWord = UCase(Trim(aWords(i)))
    strNextWord = UCase(Trim(aWords(i + 1)))
    If strWord = strNextWord Or strWord & "," = strNextWord Or strWord & "." = strNextWord Or strWord & ";" = strNextWord Then
     ' Debug.Print strWord & " " & strNextWord
      aWords(i) = ""
    ElseIf strWord = strNextWord & "," Or strWord = strNextWord & ";" Then
      'Debug.Print strWord & " " & strNextWord
      aWords(i + 1) = ""
    End If
  Next i
  
  'put back together
   For Each itm In aWords
     If removeDups = "" Then
       removeDups = itm
     Else
       removeDups = removeDups & " " & itm
     End If
     removeDups = Replace(removeDups, "  ", " ")
   Next itm
End If
End Function
[code]