AnsweredAssumed Answered

Use Field Calculator to strip out "1/2" in an Address

Question asked by bokeefe on Mar 18, 2015
Latest reply on Mar 18, 2015 by bokeefe

I'm busy processing 200,000+ addresses that incorrectly have an old outdated sub-designation in use. Our City no longer uses the "1/2" in our addresses. We have updated them to a sub-designation of "REAR" instead.


So whereas previous an address might have been "1234-1/2 East Main Street South" it is now listed as "1234 East Main Street South Rear" and I am trying to use the Field Calculator to remove this.


Here is my code.

DIM StripStart, StripEnd
DIM DataToStrip, DataToStripRight, DataToStripLeft, DataToStripLen
DIM WhatToStrip, WhatToStripLen
DIM NewDataSet

WhatToStrip = '1/2'
WhatToStripLen = Len( WhatToStrip )

DataToStrip = [Address]
DataToStripLen = Len( DataToStrip )

StripStart = InStr( 1, DataToStrip, WhatToStrip )
StripEnd = StripStart + WhatToStripLen

DataToStripRight = Right( DataToStrip, ( DataToStripLen - StripEnd ) )
DataToStripLeft = Left( DataToStrip, StripStart )

NewDataSet = DataToStripLeft + ' ' + DataToStripRight

I am getting a 999999 error that says it is undefined. Can anyone take a look at this and see what I'm doing wrong?


Message was edited by: Brian O'keefe Cleaned up the Code. It has compiled into a single line due to missing BR tags.