Need procedure to replace a character in a string

[Bob Valentine]

Group:

I have a form that requests the user to enter the company name. However, if
the user enters a period ( . ) in the company name, I want to take it out. I
have developed the following code to take out the period, but it inserts a
space where the period was. How do I remove the period and also take out the
space that the period held?

Here is my code:

nPos1 = InStr(1, CompanyName, ".", vbTextCompare)
If (nPos1) > 0 Then
MsgBox "The company name contains an invalid character. "
If nPos1 > 0 Then
While nPos1 > 0
nPos1 = InStr(1, NewCompanyName, ".", vbTextCompare)
Mid$(NewCompanyName, nPos1, 1) = " "
Wend
End If

Any suggestions will be greatly appreciated.

Bob V

 

[Douglas J. Steele]

Assuming you're using Access 2000 or newer, there's a built-in function
named Replace.

Replace(CompanyName, ".", "")

 

[Bob Valentine]

Doug:

Thanks so much. You have been very helpful.

Bob V