Replace "," with " "

[jgelpi16]

I need to run an update query to update any "," in a database to " ". The reason being is I am going to be exporting the data to a text file that is comma delimmited.

Dim i as integer
Dim varPos As integer
Dim varFindChar as string
Dim varLookUp as String

varLookUp = DLookup()
FindChar = "s"

For i = 1 To Len(varLookUp)
If Mid(varLookUp, i, 1) = FindChar Then
Pos = i
End If
Next i

[DaveT]

I would use update query using Replace such as
Replace([myTextField], ",", " ")

[TheShabz]

also, if you open up the table, and hit ctrl+H, you open the "find and replace" dialog box =P. Remember, Access is still a part of MS Office

[weekend00]

also, if you open up the table, and hit ctrl+H, you open the "find and replace" dialog box =P. Remember, Access is still a part of MS Office

can you replace a "," within other words by this way?

[ajetrumpet]

I need to run an update query to update any "," in a database to " ".

if this has to be done to all tables in a database, you'll want to loop the tables within a function and either write the EXECUTE command of the DATABASE object, or use the RUNSQL command without warnings.

[TheShabz]

can you replace a "," within other words by this way?

Yea, just make sure you choose "any part" instead of "whole field". and if you put a space in the replace section itll replace with a space

[ajetrumpet]

Folks,

Always remember to backup or copy objects that you are modifying like this. Mistakes are very common when doing F & R's, and even UDF's like this. And they can't be undone in Access!

[jgelpi16]

Thanks for all the feedback. I had gotten so engrossed in the project I had completely forgotten about such a simple solution as F&R! However, this is going to be part of a process so I need something I can save (like a query or VBA code). I think I will use the update query approach. Thanks for all your help.

[jgelpi16]

DaveT - Especially like your little picture! Thanks.