Find period and replace with " " 1

[DrMingle]

I am looking for a solution that would allow me to do the following:

Find [.] and replace ['']<--this is intended to be a blank value. I thought I could search for Chr(46) so I wouldn't need to worry about spaces in front or behind the period.

I would like the search to be of the entire table, not just one field or column. It needs to be global.

I am working in MS Access 2003 and have been running into a "you can't replace the current value of the field with the replacement text" statement; this information box presents after about 58,000 replacements.

Any help would be appreciated...

 

[MajP]

UPDATE
tblTest
SET
tblTest.fldone = Replace([fldOne],".",""),
tblTest.fldTwo = Replace([fldTwo],".",""),
tblTest.fldThree = Replace([fldThree],".",""),
tblTest.fldFour = Replace([fldFour],".","");

Where fldXXX are your field names.

 

[DrMingle]

This is the information form I get with the code supplied:
"Microsoft Office Access was unable to update all the records in the update query"

"Microsoft Office Access didn't update 77,198 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations."

Write before these messages where statements stating I didn't have enough memory....but that I could still continue.

Is there a way I can do the find/replace in stages rather then having all my resources eaten up...like every 1,000 occurences make the change and then keep processing the anew for the next 1,000 occurances.

Thanks for the feedback.

 

[TheAceMan1]

How are ya DrMingle . . .

Could you give a small critique on your resolution for those who were following this thread?

I also derived VBA code that loops thru the columns detecting text data types. This relieves you of having to know the field names ... just my 2 cents . . .

[blue]   Dim db As DAO.Database, tdf As TableDef, fld As Field, SQL As String
   
   Set db = CurrentDb
   Set tdf = db.TableDefs("tblReplaceTest")
   
   For Each fld In tdf.Fields
      If fld.Type = dbText Then
         SQL = "UPDATE [purple][b]tblTest[/b][/purple] " & _
               "SET [" & fld.Name & "] = Replace([" & fld.Name & "],'.','');"
         db.Execute SQL, dbFailOnError
      End If
   Next
   
   Set tdf = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Golom]

Some data types will raise this error if you try to convert them with Replace. Notably, DateTime fields will not convert because the result may not be a valid date after conversion.

You should run Replace only on Text fields as TheAceMan1 has done.

If you want to run it on numerics then convert the numeric to text explicitly before running Replace. For example

myNumField = Val(Replace ( Format(myNumField, "0.000"), ".", ""))