Find and Replace on a huge amount of records

[Kai90]

Hello all,

I am trying to find and replace certain characters that are in every field of table.

The catch is: The table has 600K records in it.
So when I use CTRL H (Find and Replace), it works great....but only for the first 10000 records or so. It won't do any after that.

Then I found a module:

Public Function fReplace(ByVal Expression As String, ByVal Find As String, ByVal Replace As String) As String
Dim strTmp As String, n As Integer

strTmp = Expression
n = InStr(strTmp, Find)

Do While n > 0
strTmp = Left(strTmp, n - 1) & Replace & _
Mid(strTmp, n + Len(Find))
n = InStr(n + Len(Replace), strTmp, Find, 1)
Loop

fReplace = strTmp

End Function

Source: http://www.access-programmers.co.uk/forums/showthread.php?t=142513

Then I call this function in my update query in the "update to" row.


While this works great, the problem I now face is, that the characters I try to get rid off, are a quotation marks (")

My update query looks like this:

fReplace([Field5],""","")

Of course this throws an "invalid String" error, because you cannot put a quotation mark in that spot.

Any ideas on how to replace quotation marks??? As said, this works great with all other characters....just not the quotation marks.


Thanks a lot,

Kai

 

[boblarson]

Chr(34) is a double quote. So you can use

fReplace([Field5], Chr(34), "")

 

[Kai90]

Wow, I learn something new everyday!

This works great!! Thank you Bob!