Export without Punctuation in field

[cav]

I have a query that includes Fname, Lname, Address. I need to export these fields without commas or any punctuation. So RD. 2, BOX 72 should be exported as RD BOX 72. I am not a programmer so if code is needed, please be specific. I thank you for considering my problem.

 

[jebry]

Hi!

You could try something like this:

Public Function ExtractCharacter(WrongString As String) As String
'This function will extract the all occurances of punctuation

'Declare local variables
Dim ClearedString As String
Dim CharPos As Long
Dim BadChar As String
Dim Counter As Long

'Main Loop
ClearedString = WrongString
For Counter = 33 to 47
BadChar = Chr(Counter)
CharPos = InStr(ClearedString, BadChar)
Do Until CharPos = 0
ClearedString = Left$(ClearedString, CharPos - 1) & Mid$(ClearedString, CharPos + 1)
CharPos = InStr(ClearedString, BadChar)
Loop
Next Counter

'Set the function
ExtractCharacter = ClearedString

End Function

The Chr function returns the character which corresponds to the ascii value supplied. 33 to 47 is main set of punctuation marks, but look up the ascii character set and use any that seems appropriate to you.

hth
Jeff Bridgham

 

[cav]

Thanks so much. I'll be working on this...