Remove ";"

  • Thread starter Thread starter JA
  • Start date Start date
J

JA

My concatenated field CombModule has entries
like ;PM;;;LE;;;;;
and ;PM;;PP PL;PP OP;PP PK;;;;;;QM

How can I get the entries to be
like PM;LE;
and PM;PP PL;PP OP;PP PK;QM

in a new field called CombModule01

Help please.
 
Public Function Multi2Single(ByVal strInput As String, ByVal strChar As
String) As String

Dim strWork As String

strWork = strInput
Do While InStr(1, strWork, String$(2, strChar)) <> 0
strWork = Replace(strWork, String$(2, strChar), strChar)
Loop
If Left$(strWork, 1) = strChar Then
strWork = Mid$(strWork, 2)
End If
If Right$(strWork, 1) = strChar Then
strWork = Left$(strWork, Len(strWork) - 1)
End If
Multi2Single = strWork

End Function

Example of use in Immediate window ...

? multi2single(";one;;two;;;three;;",";")
one;two;three

Example of an update query using this function ...

UPDATE tblTest SET tblTest.NewField = Multi2Single([OldField],";")
WHERE (((tblTest.NewField) Is Not Null));
 
If this is a one time effort, you can use some update queries to replace each
occurence of two semi-colons with one semi-colon.

What version of Access are you using? If Access 2000 with service packs or
later versions, there is a replace function.

First, move the existing value over
UPDATE YourTable
Set CombModule1 = CombModule

Now, start cleaning it up
UPDATE YourTable
Set CombModule1 = Replace(CombModule1,";;";")
WHERE CombModule1 Like "*;;*"

Repeat above until no records get updated, then you can strip off the leading semi-colon.

UPDATE YourTable
Set CombModule1 = Mid(CombModule1,2)
WHERE CombModule1 Like ";*"

You could also write a VBA function to do each entry in one action. This would
probably be the better solution, if you need to do this repeatedly. Using the
UNTESTED VBA function below.

UPDATE YourTable
Set CombModule1 = StripDoubles(CombModule)


UNTESTED AirCode follows.

'======= Paste the following into a module and save the module with
'======= some name other than StripDoubles
Function StripDoubles (strIn)
Dim strOut as String

If Len(strIn & vbnullstring) = 0 then
StripDoubles = strIN
Else
For I = 1 to Len(strIn)-1
If Mid(strIn,I) <> ";" Then
strOut = strOut & Mid(strIn,I)

Elseif Mid(strIn,I+1) = ";" Then
'Skip multiple semicolons
Else
strOut=StrOut & Mid(strIn,I)
End if
Next I

If Left(StrOut,1) =";" then
StrOut= Mid(strOut,2)
End If

StripDoubles = strOut
End If

End Function
 
Back
Top