Removing Commas

[philljp390]

Hi all,

Could someone please help, as an example, i have a string in one field:

5, Woodlane, England

I want some kind of query that will delete the commas so it would just be:

5 Woodland England

Hope someone can help.

Thanks

 

[dcx693]

Have you tried searching the forum? This sounds like it would be a common question.

 

[Uncle Gizmo]

' This function removes spaces from the text
Public Function ReplaceSpace(StrRemoveSpace As String)

Let ReplaceSpace = Replace(StrRemoveSpace, " ", "", 1, -1, 1)

End Function


And if you put a comma where the space is it should remove that...

 

[sdkramer]

how do you use these functions. I mean where do you type them in, etc.

 

[Uncle Gizmo]

First of all, THIS function, (as it uses "Replace") you need Access 2000 or greater.

Copy the function in to a module.

Make a blank form, place on the form 2 text boxes, txt1 and txt2.

For the source of txt2 put ReplaceSpace(txt1) or =ReplaceSpace(txt1) maybe?


Put a command button on the form to refresh txt2

Type some text in to txt1 and presss the command button, txt2 should display the text without the spaces.

I may have missed something out so if it don't work repost and I'm sure someone will know how to fix it...