ADDRESS_1 (BEFORE ) 76TH ST AVE

[EBee]

ADDRESS_1 (BEFORE )

76TH ST AVENUE
8808 RIVER PINES CT AVENUE 101
8730 CEDROS AVENUE APT G

ADDRESS_1 ( AFTER RUNNING THE CODE)

76TH ST VENUEAVE
8808 RIVER PINES CT AVENUE 101
8730 CEDROS VENUE APT GAVE

Can someone take a look at this code: I want to search and replace AVENUE with AVE. I am aware of the EDIT>REPLACE but i really would like to do this in VBA. I am Usesing 97. . . thanks


Dim Rep As String
Dim strTemp As String
Dim intAt As Integer
Dim intLength As Integer

Dim rs As Recordset
Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("AddressP&quot


rs.MoveFirst
Do Until rs.EOF


intLength = Len(rs!ADDRESS_1)
If InStr(rs!ADDRESS_1, "AVENUE&quot > 0 Then

For intAt = 1 To intLength

If Mid(rs!ADDRESS_1, intAt, Len("AVENUE&quot) = "AVENUE" Then
strTemp = strTemp + ( "AVE&quot

Else
strTemp = strTemp & Mid(rs!ADDRESS_1, intAt, 1)
End If

Next intAt
rs.Edit
rs!ADDRESS_1 = strTemp
Rep = strTemp
rs.Update

rs.MoveNext
End If
rs.MoveNext
strTemp = ""

Loop

MsgBox " UPDATE DONE"
End Sub

 

[THoey]

Won't the following work?

   Dim intLength As Integer
   Dim intLoc As Integer
   Dim rs As Recordset
   Dim db As Database
 
   Set db = CurrentDb
   Set rs = db.OpenRecordset("AddressP")

   rs.MoveFirst

   Do Until rs.EOF

      intLength = Len(rs!ADDRESS_1)
      IntLoc = INSTR(rs!ADDRESS_1, "AVENUE")

      IF IntLoc > 0 THEN
         rs.EDIT
 
         IF intLength = IntLoc + 6 THEN
            rs!ADDRESS_1 = LEFT(rs!ADDRESS_1, IntLoc) & "AVE."
         ELSE
            rs!ADDRESS_1 = LEFT(rs!ADDRESS_1, IntLoc) & "AVE." & RIGHT(rs!ADDRESS_1, intLength - (intLoc + 6))
         END IF

         rs.Update
      END IF
 
      rs.MoveNext

   Loop

   MsgBox " UPDATE DONE"

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[EBee]

thanks for responding

i tried the code and it is bombing out after the else for some reason:

If intLoc > 0 Then
rs.Edit

If intLength = intLoc + 6 Then
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLoc) & " AVE. "
Else
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLoc) & " AVE. " & Right(rs!ADDRESS_1, intLength - (intLoc + 6))
End If

rs.Update
End If


i think if the intLenght is less than the intLoc + 6 it will give a negative value, and it does not like that. I then reverse the order and it worked for the first record then bombed again on the second record because of the same reason. What should I do so it will only give the diff?

thanks

 

[THoey]

Sorry, does this work?

If intLength = intLoc + 6 Then
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLoc) & " AVE. "
Else
If intLength = intLoc + 6 Then
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLoc) & " AVE. "
Else
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLoc) & " AVE. " & Right(rs!ADDRESS_1, intLength - (intLoc + 6))
End If
End If
Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[EBee]

almost there, I am still getting a " Invalid procedure call argument" error.

and it is happening after the else

rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLoc) & " AVE. " & Right(rs!ADDRESS_1, intLength - (intLoc + 6))

inLenght = 22
intLoc = 17

this is the value in the debug window

 

[THoey]

Try changing it too:

If intLength = intLoc + 5 Then
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLoc) & "AVE."
Else
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLoc) & "AVE." & Right(rs!ADDRESS_1, intLength - (intLoc + 6))
End If

Sorry, don't really have time to test it for you. But this should work. If not, I am sure someone else will tweak it to make it work... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[EBee]

thanks for your help

i finally got it to work: Here the code


rs.MoveFirst
Do Until rs.EOF

intLength = Len(rs!ADDRESS_1)
intLocAve = InStr(rs!ADDRESS_1, "Avenue&quot
If intLocAve > 0 Then
rs.Edit
If intLength = intLocAve + 6 Then
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLocAve - 1) & " AVE "
Else
If intLength = intLocAve + 5 Then
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLocAve - 1) & " AVE "
Else
rs!ADDRESS_1 = Left(rs!ADDRESS_1, intLocAve - 1) & " AVE " & Right(rs!ADDRESS_1, intLength - (intLocAve + 6))

End If
rs.Update
End If
End If
rs.MoveNext
Loop

thank you very much. . this is grest stuff
erwin