Look for 2 characters and replace 1

[AKMonkeyboy]

I have a string that contains (maybe) "; ". If it contains that, I want it removed. I have code that searches for and removes 1 character (a "0" or a "-" for example), but I am unable to figure out how to remove instances of 2 characters (I can't run the code twice and peel off one at a time - that won't work.

In a nutshell I'd like

1st word; ; ;2nd word; ; 3rd word;

to appear as
1st word; 2ndword; 3rd word;

Any ideas?

 

[JohnYingling]

If Access 2000,

strW = Replace(strIn,"; ","&quot

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[BillPower]

Hi AKMonkeyboy,

I thought you wanted your string to read:
1st word; 2ndword; 3rd word; so I wrote this.

Paste the following into a Global Module:

Function ReplaceMyCharacter(strString As String)
Dim strChar As String
Dim intLen As Integer
Dim intPrevPos As Integer
Dim intCurrPos As Integer
strChar = ";"

intLen = Len(strString)
intPrevPos = 1
While intCurrPos < intLen
intCurrPos = InStr(intPrevPos, strString, strChar)
If intCurrPos - intPrevPos <= 1 Then
If Mid(strString, intCurrPos + 1, 1) = &quot; &quot; Then
strString = Mid(strString, 1, intCurrPos - 2) & Mid(strString, intPrevPos + 2, intLen - (intPrevPos))
intCurrPos = intCurrPos - 2
Else
strString = Mid(strString, 1, intCurrPos - 1) & Mid(strString, intPrevPos + 2, intLen - (intPrevPos))
intCurrPos = intCurrPos - 1
End If
intLen = Len(strString)
End If
intPrevPos = intCurrPos + 1
Wend
ReplaceMyCharacter = strString
End Function

To test it, paste this into the on click event of a button on a form:

MsgBox ReplaceMyCharacter(&quot;1st word; ; ;2nd word; ; 3rd word;&quot

To use it in a query the syntax would be:

ReplaceMyCharacter([YourFieldName])

The idea behind this was to leave 1 semi-colon as in your example and also remove the extra spaces left when the semi-colons are removed. Might come in handy for you or another member in the future.

 

[AKMonkeyboy]

Hey billpower

In the pursuit of useful code I tried yours too (the Replace function from John worked well). When I tried yours- I got a debug message:
Invalid call or argument

It then brought me to this area of the code:
strString = Mid(strString, 1, intCurrPos - 1) & Mid(strString, intPrevPos + 2, intLen - (intPrevPos))

Something I'm missing?

 

[BillPower]

Hi AKMonkeyboy

This was happening where there was only 1 or no &quot;strChar&quot; in the string, hopefully have remedied this below.

Function ReplaceMyCharacter(strString As String)
Dim strChar As String
Dim intLen As Integer
Dim intPrevPos As Integer
Dim intCurrPos As Integer
strChar = &quot;;&quot;

intLen = Len(strString)
If intLen > 0 And InStr(1, strString, strChar) > 0 Then
intPrevPos = 1
intCurrPos = 1
While intCurrPos < intLen
intCurrPos = InStr(intPrevPos, strString, strChar)
If intCurrPos - intPrevPos <= 1 Then
If Mid(strString, intCurrPos + 1, 1) = &quot; &quot; Then
strString = Mid(strString, 1, intCurrPos - 2) & Mid(strString, intPrevPos + 2, intLen - (intPrevPos))
intCurrPos = intCurrPos - 2
Else
strString = Mid(strString, 1, intCurrPos - 1) & Mid(strString, intPrevPos + 2, intLen - (intPrevPos))
intCurrPos = intCurrPos - 1
End If
intLen = Len(strString)
End If
intPrevPos = intCurrPos + 1
Wend
ReplaceMyCharacter = strString
ElseIf intLen > 0 Then
ReplaceMyCharacter = strString
End If
End Function

Thanks for the feedback, Bill