Update Query to clean up data in text fields

[kschomer]

I am trying to clean up an address field where the address includes PO Box.  I narrow down my update by setting criteria, but then how do I update PO box without touching the various numbers.  I added a new field called revised address 2 and tried using an update query "P.O. Box" + abs[tablename],[fieldname] but the Abs function did not work.  I know there must be an easy way to do this via a query without manually updating the field.  

 

[kathryn]

Could you please give an example of what the data currently looks like (a few rows) and what you would like it to look like?&nbsp;&nbsp;Unless this is something that must be automated, I have often found that a simple search and replace is the easiest way to clean up address fields.<br><br> <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[kschomer]

It wouldn't be a simple search/replace 'cause the data is all over the place.&nbsp;&nbsp;A few examples would be:<br>P o box 856<br>p o box 1324<br>box 987<br>P O Box 765<br>P.O. Box 6544<br>P.o. Box 543<br>PO box 321

 

[kathryn]

OK, it won't be simple, but it is doable!&nbsp;&nbsp;;-)<br><br>I am assuming that all data in that column is PO Box data.&nbsp;&nbsp;If not, it is a bit more complex.<br><br>The method I would use is to replace all occurances of P,O,Box, comma and space, in that column ONLY, with a nonsense character, such as %&nbsp;&nbsp;(there cannot be any naturally occurring instances of % in the column for this to work)<br><br>Then replace all multiple instances of % with a single %.&nbsp;&nbsp;The easiest way is to search on %% and replace with %.&nbsp;&nbsp;Run this a few times until no more are replaced and it will replace all multiples and leave a single %.<br><br>Then replace the singe % with &quot;P. O. Box &quot;&nbsp;&nbsp;or whatever.<br><br>It might sound complex, but it really isn't too bad. <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[kschomer]

I am sorry Kathryn, but I don't understand P,O,Box,comma and space.&nbsp;&nbsp;The Edit/Replace or the Update to: didn't understand the syntax either.&nbsp;&nbsp;Using the % won't be a problem.&nbsp;&nbsp;But, I am not sure what the comma after P and comma after O and comma after box and comma and space mean.&nbsp;&nbsp;Can you explain?&nbsp;&nbsp;Karen<br>

 

[MichaelRed]

Karen,<br><br>If you can do a simple function, just throw away everthing which is NAN (not) a number and prefix the results w/ &quot;P.O. Box &quot;<br><br>Public Function basFixPoNum(PoNum As String) As String<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Idx As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim NewPoNum As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim MyChar As String * 1<br><br>&nbsp;&nbsp;&nbsp;&nbsp;For Idx = 1 To Len(PoNum)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Do the whole enchalida<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MyChar = Mid(PoNum, Idx, 1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Get one char<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'See if it is numeric<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If (MyChar &gt;= &quot;0&quot; And MyChar &lt;= &quot;9&quot Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'If So, add to pile<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NewPoNum = NewPoNum & MyChar<br>&nbsp;&nbsp;&nbsp;&nbsp;Next Idx<br><br>&nbsp;&nbsp;&nbsp;&nbsp;basFixPoNum = &quot;P. O. Num &quot; & NewPoNum<br><br>End Function<br><br><br>Paste this into a Public code module and 'call' it from your query:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;PoNum: basFixPoNum([PoNumField])<br><br>It should take care of the examples shown.&nbsp;&nbsp;If there are some patterns NOT SHOWN, it may need some additional work.&nbsp;&nbsp;The only one forsee is to have a ponum with a character embeded (e.g. P.O. Box 948-A or P.O. Box 947B ...)<br><br>MichaelRed<br>There is never time to do it right but there is always time to do it over

 

[kathryn]

Karen, <br><br>Sorry, I didn't mean to confuse you.&nbsp;&nbsp;What I meant was that you could do a series of search/replace.&nbsp;&nbsp;The first time you would replace all P with %, the next time replace all O with %, etc.&nbsp;&nbsp;The idea was to replace everything that wasn't a number with a % and then to replace all the % with one % and then replace that with a &quot;P. O. Box&quot; or whatever.<br><br>The code that Michael posted will also do this, much more elegantly. <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>