module clean data

[EBee]

I am trying to clean the data from my address field and wanted to do somthing like this:


Sub Add_Update()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Address&quot


rs.MoveFirst
Do Until rs.EOF
If rs!Add2 = Null Then
rs.MoveNext

ElseIf rs!Add2 Like "*Avenue*" Then

rs.Edit
rs!Add2 = "*Ave.*"
rs.Update

ElseIf rs!Add2 Like "*Street*" Then

rs.Edit
rs!Add2 = "*St.*"
rs.Update



End If

rs.MoveNext
Loop

MsgBox "Update is Done . . . . Thank you"



End Sub

The problem is that its replacing th whole filed with "Ave. or St.. . .

how can I re-write the module so it only replaces the word within the string in the address field,

thanks
erwin

 

[lionelbrown]

You can use the Replace function to replace text in a string.

Check the Access help files for more info on the Replace function.

 

[EBee]

when I go to help i don't see what I need. ( Access 97) I just need a starting ground so i create search and replace module modify it so i can add more than one search and replace.

I got this one on-line and I don't understand what its doing


Dim strWork As String
Dim strL As String
Dim strR As String
Dim intX As Integer

strWork = pstrIn

Do
intX = Nz((InStr(1, strWork, pstrOld, vbTextCompare)), 0)
If intX > 0 Then
strL = Left$(strWork, intX - 1)
strR = Right$(strWork, (Len(strWork) - intX - Len(pstrOld) + 1))
strWork = strL & pstrNew & strR
Else
Exit Do 'Escape from the loop if nothing else to do
End If
Loop

StrReplace = strWork

End Function

thanks in advance for explanation of this code or other help information


erwin

 

[lionelbrown]

OK... here's some working code (I added the DAO. to Database and Recordset because I'm using Access 2000). I left your code the way it was and only modified what I needed to get it to work. I took out the rs.movenext from the initial if test (rs!add2 = Null) because you would move to the next record and then move to the next record again right before Loop (thus skipping a record).


Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Address&quot

rs.MoveFirst
Do Until rs.EOF
If rs!Add2 = Null Then

ElseIf rs!Add2 Like "*Avenue*" Then

rs.Edit
rs!Add2 = Replace(rs!Add2, "Avenue", "Ave.&quot
rs.Update

ElseIf rs!Add2 Like "*Street*" Then

rs.Edit
rs!Add2 = Replace(rs!Add2, "Street", "St.&quot
rs.Update

End If

rs.MoveNext
Loop

MsgBox "Update is Done . . . . Thank you"

 

[BoxHead]

Erwin,

This sounds like more than you were asking for, but it loops through the records checking for 'Avenue', 'Street', 'Road', 'Court', etc...

The function uses two Choose statements: looks for values in the first and replaces with values from the second.

You can paste the function into its own module and then call it from a query or control as
= ClnAddr([FieldName])

[b]
Public Function ClnAddr(MyString as String) As String
 Dim OldItem as String 
 Dim NewItem as String
 Dim NewVal as String
 Dim i as Integer

NewVal = Nz(MyString, "")
  For i = 1 to 10
OldItem = Choose(i, ".", " Avenue", " Road", " Court", " Street", " Boulevard", " Lane", " Highway", " Place", " Drive")
NewItem = Choose(i, "", " AVE", " RD", " CT", " ST", " BLVD", " LN", " HWY", " PL", " DR")

Do
If InStr(NewVal, OldItem) = 0 Then
Exit Do
Else
NewVal = Left(NewVal, Instr(NewVal, OldItem -1) & NewItem & Mid(NewVal, InStr(NewVal, OldItem) + Len(OldItem))
End If
Loop
Next i
ClnAddr = Trim(NewVal)

End Function [/b]

This works but could definitely use some cleaning up. Notice that i = 1 to 10. If you add any items to the Choose statements you have to change the uppermost i-value.


John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Ignore the at the beginning of the function and the at the end. I was trying to combine the TGML tags and they didn't take.

Sorry John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Once more...

Ignore the bracketed letter 'b' at the beginning of the function and the bracketed slash & letter 'b' at the end. I was trying to combine the TGML tags and they didn't take.

Is there a forum for TGML? :~/ John

Use what you have,
Learn what you can,
Create what you need.