Eliminate commas from beginning and end of strings 4

[puttergirl]

I have a form set up where you select many items from a list and when you save it, it transfers the values into one field on another form separated by commas. Sometimes after changes, I end up with commas at the beginning or end of the string. I would like a function that would remove the commas. For example, I end up with a string like this:
, ROUTINE, TOXICOLOGY, OTHER,

The name of the field is CLINICAL CHEMISTRY. I'm aleady using TRIM to delete spaces from the beginning and end. I've tried using REPLACE and can't get it right. I've tried using MID to find the first comma and LEN to count characters and then use that to delete the last character. I've had more success removing that last period, but none on the first.

Does anyone know of a simple function for this?

 

[FancyPrairie]

Try this:

Function RemoveCommas(ByVal strRemove) As String

    If (Left(strRemove, 1) = ",") Then strRemove = Mid(strRemove, 2)
    If (Right(strRemove, 1) = ",") Then strRemove = Mid(strRemove, 1, Len(strRemove) - 1)
    RemoveCommas = Trim(strRemove)
    
End Function

 

[TheAceMan1]

How are ya puttergirl . . .

Perhaps this:

[blue]Public Function NoComma(ByVal strDat)
   NoComma = Trim(Replace(strDat, ",", " "))
End Function[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[FancyPrairie]

AceMan,

Either you or I misunderstood the question. I assumed 2 things. 1st that the string was already trimmed and 2nd that she just wanted to remove the beginning and ending commas only.

 

[puttergirl]

Thanks to both of you. I love both. I'm not good at writing code. Could one of you please help me combine statements into 1 module. First, I want it to take any multiple commas and change it to just 1, such as:

ROUTINE, , , TOXICOLOGY

I had written a function:
Replace([CLINICAL CHEMISTRY],", ,", ",")
but I don't know how to loop it.

Then I want it to eliminate any spaces in front of a comma, such as ROUTINE , TOXICOLGY

and then finish with the wonderful RemoveCommas function which worked like a charm. THANKS.

 

[puttergirl]

Bottom line is... I need a function that will fix all kinds of comma errors so that the finished product contains only 1 comma and space after each value. So it will take a cell like this:

, ROUTINE, , , TOXICOLOGY, TCM , OTHER (URINALYSIS),

and turn it into this:

ROUTINE, TOXICOLOGY, TCM, OTHER (URINALYSIS)


THANKS, THANKS, THANKS.

 

[softhemc]

You could try something like ...

Public Function ResolveCommas(source As String) As String

  Dim bits() As String
  Dim result As String
  Dim a As Integer
  
  bits = Split(source, ",")
  result = ""
  
  For a = 0 To UBound(bits)
    If Trim(bits(a)) <> "" Then
      result = result & Trim(bits(a)) & ", "
    End If
  Next a
  
  ResolveCommas = StrReverse(Mid(StrReverse(Trim(result)), 2))

End Function


Private Sub TestResolveCommas()

  MsgBox (ResolveCommas(", ROUTINE, , , TOXICOLOGY, TCM , OTHER (URINALYSIS), "))

End Sub

 

[FancyPrairie]

Aceman, looks like we were both wrong.

You could probably use a Reg Exp to do it but I'd have to search the net for an example. However, the following works:

Function RemoveCommas(ByVal strRemove As String) As String

    Dim strTemp As String
    Dim bolDone As Boolean
    Dim aList() As String
    Dim i As Integer
    
    '********************************************
    '*  Get rid of beginning and ending commas  *
    '********************************************
    
    strRemove = Trim(strRemove)
    If (Left(strRemove, 1) = ",") Then strRemove = Mid(strRemove, 2)
    If (Right(strRemove, 1) = ",") Then strRemove = Mid(strRemove, 1, Len(strRemove) - 1)
    
    '********************************************
    '*  Get rid of space comma and comma comma  *
    '********************************************
    
    strTemp = vbNullString
    bolDone = False
    
    Do While Not bolDone
        strTemp = Replace(strRemove, " ,", ",")
        strTemp = Replace(strTemp, ",,", ",")
        If (strTemp = strRemove) Then bolDone = True
        strRemove = strTemp
    Loop
    
    '***********************************
    '*  Remove all spaces around name  *
    '***********************************
    
    aList = Split(strRemove, ",")       'Create Array
    For i = 0 To UBound(aList)
        aList(i) = Trim(aList(i))       'Trim each name
    Next
    
    strRemove = Join(aList, ",")        'Convert array to string
    
    '***************************************
    '*  Finally, make it comma space name  *
    '***************************************
    
    RemoveCommas = Trim(Replace(strRemove, ",", ", "))  'Add space after comma and return results to caller
        
End Function

 

[FancyPrairie]

softhemc, you're method is certainly easier.

 

[softhemc]

FancyPrairie, unfortunately it is something that I have to do too regularly to combat careless users

 

[ProgramError]

As a matter of interest how was the string created?
Perhaps it is the point of cration where the problem lies and all this extra coding could be avoided.



Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[TheAceMan1]

How are ya softhemc . . .

[blue]Good Job![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[puttergirl]

Thanks, both of you. Both of these work great except they won't handle the null fields. I tried to put them within an IIF statement but I'm having trouble with that too. Any suggestions?

 

[puttergirl]

Softhemc, you're right. I know I have a problem at the point that the string is saving to the table. But right now I've created such a complicated program with the string coming from so many sources, I don't want to take a chance of breaking anything. At least for now I want to be able to get the data into the report correctly and then later working on searching out the problems.

 

[puttergirl]

I've tried this but its not working:

[Forms]![Laboratory License]![CLINICAL CHEMISTRY] = IIf(IsNull([Forms]![Laboratory License- Cats2]![Astr]), "", RemoveCommas([Forms]![Laboratory License- Cats2]![Astr]))

 

[softhemc]

Does this help with the nulls ...

Note the change from:
[tt]Public Function ResolveCommas(source As String) As String
[/tt]
to:
[tt]Public Function ResolveCommas(source As Variant) As String[/tt]

Public Function ResolveCommas(source As [b]Variant[/b]) As String

  Dim bits() As String
  Dim result As String
  Dim a As Integer
  
  If IsNull(source) Then
    ResolveCommas = ""
  Else
    bits = Split(source, ",")
    result = ""
    
    For a = 0 To UBound(bits)
      If Trim(bits(a)) <> "" Then
        result = result & Trim(bits(a)) & ", "
      End If
    Next a
    ResolveCommas = StrReverse(Mid(StrReverse(Trim(result)), 2))
  End If

End Function


Private Sub TestResolveCommas()

  MsgBox ResolveCommas(", ROUTINE, , , TOXICOLOGY, TCM , OTHER (URINALYSIS), ")
  MsgBox ResolveCommas(Null)

End Sub

 

[puttergirl]

THANKS, THANKS, THANKS everyone. That one worked. You're life savers!!!!

 

[TheAceMan1]

[blue] ... Both of these work great except they [purple]won't handle the null fields.[/purple] ...[/blue]

[blue]Be more specific about this.[/blue] Can't tell if you mean a null or zero length string within the test string (a comma with no preceding value) or the value of [blue]CLINICAL CHEMISTRY[/blue] or what.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[puttergirl]

Thanks, TheAceMan1. It was the field the string went into but I've got it now. Thanks.

 

[ZmrAbdulla]

How about changing it from the beginning.

Private Sub List2_AfterUpdate()
    Dim x As Integer
    Dim str As String
    For x = 0 To Me.List2.ListCount - 1
        If Me.List2.Selected(x) Then
            str = str & Me.List2.ItemData(x) & ", "
        End If
    Next
    If Len(str) > 1 Then
        str = Left(str, Len(str) - 2)
    End If
    Me.Text4 = str

End Sub

This will give you coma separated list like
Item1, Item2, Item3



________________________________________________________
Zameer Abdulla
Help to find Missing people