Using Update Query to Find & Replace 1

[JohnieR]

Thanks to earlier responses, I already have useful Update Query routines to, say, fill every blank cell in a table's field with a value.

They run very quickly, so now I'd like to use UQ routines instead of Find & Replace in Table view (which, with my database's 1.4 million records, takes ages or hangs up even with 1GB+ of RAM).

Examples of wanted routines are to remove from a particular field every occurrence of a hypthen - eg, changing 123-456-789 to 123456789; or to remove from a particular field every occurrence of a full stop - eg, J.K. Rowling to J K Rowling.

Many thanks in advance for anyone who can steer me in the right direction.

 

[PaulF]

try this in the Update Query. Just Place the function in a new code Module and then in the Update Query place the Function in the Update To row, and pass it the Field name you want to Update (i.e. to Update the field MyData put ParseHyphenAndPeriod([MyData]) in the Update To row). This removes Hyphens and closes up the gap, and it replaces Periods with a space.

Function ParseHyphenAndPeriod(strIn As String) As String
Dim x As Integer
Dim strOut As String
If IsNull(strIn) Or strIn = "" Or strIn = " " Then Exit Function
For x = 1 To Len(strIn)
Select Case Mid(strIn, x, 1)
Case "."
strOut = strOut & Space(1)
Case "-"
strOut = strOut
Case Else
strOut = strOut & Mid(strIn, x, 1)
End Select
Next x
ParseHyphenAndPeriod = strOut
End Function

PaulF

 

[tlbroadbent]

If you use Access 2000 or later, you can use the built in function Replace.

Update table1
Set col1=replace(col1,"-","&quot
Where instr(Col1,"-&quot>0

Update table1
Set col1=replace(col1,"."," &quot
Where instr(Col1,".&quot>0
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.