Removing Unwanted Characters

[raskew]

I received a Private Message from a forum participant who had:

tblComments
-Comments (text)

Given that [comments] might contain:
-" ALSF/SSALR CERTIFIED " (quotes included)

The question was (using an update query)...
...how to get rid of the double quotes and extra spacing, returning:
-ALSF/SSALR CERTIFIED

Here’s one way it might be attacked:

Query:
UPDATE tblComments SET tblComments.Comments = onespace(chopit([comments],Chr(34)));

Function ChopIt(), copied/pasted to a module, gets rid of the unwanted quotes (Chr(34))

Function ChopIt(pStr As String, ParamArray VarMyVals() As Variant) As String
'*******************************************
'Name:      ChopIt (Function)
'Purpose:   Remove a list of unwanted
'           characters from a string
'Inputs:    From debug window:
'           '? chopit("(626) 123 5555", ")","(")
'Output:    626 123 5555
'*******************************************

Dim strHold As String
Dim i As Integer, n As Integer

strHold = Trim(pStr)
'check for entry
If UBound(VarMyVals) < 0 Then Exit Function
For n = 0 To UBound(VarMyVals())
   Do While InStr(strHold, VarMyVals(n)) > 0
      i = InStr(strHold, VarMyVals(n))
      strHold = Left(strHold, i - 1) & Mid(strHold, i + 1)
   Loop
Next n
ChopIt = Trim(strHold)
End Function

...and Function Onespace() takes care of the excess spaces:

Function onespace(pStr As String)
'*******************************************
'Name:      onespace (Function)
'Purpose:   Removes excessive spaces from a string
'Inputs:    call onespace(" the    quick    brown fox")
'Output:    "the quick brown fox"
'*******************************************

Dim strHold As String
strHold = RTrim(pStr)
Do While InStr(strHold, "  ") > 0
  strHold = Left(strHold, InStr(strHold, "  ") - 1) & Mid(strHold, InStr(strHold, "  ") + 1)
Loop
onespace = Trim(strHold)
End Function

HTH

Bob

 

[ekta]

Thanx for replyin Raskew. I tried your function and it works great. Here is another function that removes both xtra spacing and double quotes.

Public Function SingleSpace(InText As String) As String
Dim S As String
S = InText
Dim D As String
Do
S = Replace(S, " ", " ")
Loop Until InStr(1, S, " ") = 0
SingleSpace = S

D = SingleSpace
Do
D = Replace(D, Chr(34), "")
Loop Until InStr(1, D, "Chr(34)") = 0
SingleSpace = D

End Function

Thanx again
Ekta