Solved: Replacing charcters in a String

[lawsonbooth]

I have the following code and I cannot get it to work - any help would be greatly appreicated.

I am trying to look for all of a given character and replace them with nulls
so that the characters in the string will all be run together.

Example: Columbus, GA, XXXX would be ColumbusGAXXXX

vB code

Option Compare Database
Option Explicit

Sub DoRemoveComma()
Dim x As String
Dim xSourceStr

Dim oDB As Database
Dim oRst As Recordset

Set oDB = CurrentDb()
Set oRst = oDB.OpenRecordset("AddrLine")

oRst.MoveFirst
xSourceStr = oRst![line1]

x = CharReplace(xSourceStr, ",", vbNullString)

End Sub

Public Function CharReplace(ByVal xStr As String, ByVal xFindChar As String, ByVal xReplChar As String) As String
Dim xlen
Dim n As Long

xlen = Len(xStr)

For n = 1 To xlen
If Mid(xStr, n, 1) = xFindChar Then
Mid(xStr, n, 1) = xReplChar
End If
Next n
'CharReplace = xStr
End Function

Lawson

[geekgirlau]

Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "UPDATE AddrLine SET line1 = Replace(line1,"" "","""")"
DoCmd.RunSQL strSQL

strSQL = "UPDATE AddrLine SET line1 = Replace(line1,"","","""")"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

[lawsonbooth]

Geekgirlau, thank you for your answer. I could not firgure out how to get the SQL to work inside of a function.

There is an important peice of information I failed to mentioned. My company is still using Access'97.

According to the documentation the following should work using the MID statement to replace a character. It does work for all characters except Null.

Can any one show me how to use a Null as a replacement?


x = CharReplace("Columbus,GA", ",")

Public Function CharReplace(ByVal xStr As String, ByVal xFindChar As Variant) As String

Dim xLen As Long

Dim xSearchStr As Variant
Dim n As Long
Dim xPos As Long
Dim xnull As Variant

xLen = Len(xStr)
xPos = 1
xSearchStr = xStr

xLen = Len(xSearchStr)

For n = xPos To xLen
If Mid(xSearchStr, n, 1) = xFindChar Then
Mid$(xSearchStr, n, 1) = ""
xPos = n
End If
Next n

CharReplace = xSearchStr
End Function


Lawson :banghead:

Edited 19-Oct-09 by geekgirlau. Reason: insert vba tags

[geekgirlau]

According to the documentation the following should work using the MID statement to replace a character. It does work for all characters except Null.

I'm not clear on what you're saying here. Possibilities:

The value of "xStr" might be null. Change xStr to a variant.
You are searching for a null within "xStr". Not possible - either the whole field is null or it isn't. You can't have a null inside a string value.
You want to replace the character "xFindChar" with a null value. Not possible - you can replace the character with an empty string, which is what you're currently doing.Does this answer your question? Perhaps you can provide an example of what you are expecting to be returned by your function.

[lawsonbooth]

I re-wrote the function using several other string commands to take apart and then put back the string without the commas.

Your SQL example (Greekgirlau) is great and will be a help in some SQL development I am doing.

As usual, this site is great!!!! and always a source of learning for me.

Thank you.
Lawson