Removing space using VBA

[victorab]

I am trying to remove a space from a text field [Name] in a table called RegText. The text comes in imported from a text file in this form: LastName, FirstName. After executing a function module called RemoveSpace, I was hoping to remove the space after the comma so it now becomes LastName,FirstName. However, when I call the function using a macro, my Access 2000 stops responding. Here is the code which I adapted from some users in this forum:

Function RemoveSpace()
On Error GoTo TrapError
Dim DAO_DB As DAO.Database
Dim DAO_RS As DAO.Recordset
Dim strTemp As String, strField As String
Dim x As Integer

Set DAO_DB = CurrentDb()
Set DAO_RS = DAO_DB.OpenRecordset("RegText", dbOpenDynaset)

While Not DAO_RS.EOF
If Not IsNull(DAO_RS![NAME]) Then
strField = DAO_RS![NAME]
For x = 1 To Len(strField)
If Mid$(strField, x, 1) = " " Then
strTemp = strTemp & Mid$(strField, x, 1)
End If
Next x
With DAO_RS
.Edit
![NAME] = strTemp
.Update
.MoveNext
End With
End If
Wend
ExitHere:
Exit Function
TrapError:
MsgBox Err.Description
Resume ExitHere
End Function

What am I doing wrong?

 

[Mile-O]

Forget all you've got. Put this code in a module.

Build an UPDATE Query and Update your field to this function.

So, build the query as normal selecting only the name field. Change it to an UPDATE query in the menu. In the UpdateTo: section, put: RemoveSpace([FullName])

where FullName is the name of your field.

Save the query and run it.

Public Function RemoveSpace(ByVal strData As String) As String
    Dim strText As String
    Dim intCounter As Integer
    For intCounter = 1 To Len(strData)
         If Mid(strData, intCounter, 1) <> " " Then
             strText = strText & Mid(strData, intCounter, 1)
         End If
    Next intCounter
    RemoveSpace = strText
End Function

 

[victorab]

Removing space

Thanks a lot for your solution! It works to remove all spaces in the text field. But what if I only wanted to remove the space following the comma in LastName, Firstname MiddleName, e.g. Berg, Richard Allen becomes Berg,Richard Allen and not Berg,RichardAllen?

Will modifying your code segment , as shown below, work?

If Mid(strData, intCounter, 1) <> ", " Then
strText = strText & Mid(strData, intCounter, 1)
End If

Thanks for your assistance!

 

[Mile-O]

Public Function RemoveSpace(ByVal strData As String) As String
    Dim intPosition As Integer
    intPosition = InStr(1, strData, ", ")
    If intPosition > 0 Then
        RemoveSpace = Left(strData, intPosition - 1) & Mid(strData, intPosition + 2, Len(strData) - intPosition + 2)
    Else
        RemoveSpace = strData
    End If
End Function

 

[victorab]

Your code did the job! Thanks a lot! I just modified it a little to do what I needed: remove the space after the first comma in LastName, FirstName text field and leave other spaces in string

Public Function RemoveSpace(ByVal strData As String) As String
Dim intPosition As Integer
'your line: intPosition = InStr(1, strData, ", ")
'changed to line below by removing space after ", "
intPosition = InStr(1, strData, ",")
If intPosition > 0 And Mid(strData, intPosition + 1, 1) = " " Then
'your line: RemoveSpace = Left(strData, intPosition - 1) ...
'changed to line below by removing -1
RemoveSpace = Left(strData, intPosition) & Mid(strData, intPosition + 2, Len(strData) - intPosition + 2)
Else
RemoveSpace = strData
End If
End Function

Again, thanks for your assistance.