Automating find/replace 3

[alr0]

Hi Again,

We have a procedure where we eliminate certain strings from mailing data e.g. "airmail", "*", "(", ")", etc.

I would like to find a way to accomplish this in VBA code.

Has anyone run into this before?

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[PHV]

Have a look at the Replace function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alr0]

Hi PH,

This is a great start but I was hoping to go through an entire table. Must I create a loop to look at each record and each field with replace to feed it a string or is there an easier way.

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[PHV]

I was hoping to go through an entire table
Could you please elaborate ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alr0]

Sure...

We have a table that covers may contingencies of how data is structured for mailings so we have fields for: first name, middle name, last name, company, name1, name2, name3, etc.

Presently an operator hits ctl H and sets the function to look: in all fields (whole table) and any part of field.

We then run it several times for each string to be replaced with an empty string, airmail, air mail, [*], etc.

In the future I will need to move personal data that is present in these fields e.g.

Name1 = Tom Smith Acct# 3561554
to
Name1 = Tom Smith
AcctNo = 3561554

I accomplished this using regex for a special situation and will probably need this later on (next month?) At the time someone told me, "When you solve a problem with RegEx, you then have two problems."

For right now I was hoping to eliminate certain strings without the need for regex.

Hope that clarifies things,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[MajP]

You could build a single function that takes in a "dirty" string and then use this function in a calculated field. Something like

private function cleanMailingLable(theDirtyString as String) as string
code using PHV's replace function or other string functions
cleanMailingLable = ...
end function

Then in a query

MailingLable:cleanMailingLable([tblData].[mailingDataField])

 

[raskew]

Hi -

Try this in a test table. Don't try it with your working table until you're entirely satisfied with the results.

1) Create a query, pulling down all fields that may contain an undesired word, phase or character.

2) Copy/paste the following code to a new module.

3) Change the Select query from Step 1 to an update query. In the 'Update To' cell for each field, type:

Trim(yreplaceit([FieldName],"airmail","","*",""))

... replacing FieldName with the name of your particular field.
Modify the variables as needed, e.g. the first undesired word "airmail" is followed with its replacement value of "", then "*" is followed by its replacement value of "". Expand the example as necessary.

4) Run the query. It will (should) eliminate all undesired items from all specified fields. Written and tested in A97, which doesn't have a Replace() function, but it should work with later versions.

Public Type xSec
   LeftS  As String
   MidS   As String
   RightS As String
   Total  As String
End Type

Function GetXSec(ByVal pStr As String, ByVal pdelim As String) As xSec
   GetXSec.LeftS = Left(pStr, InStr(pStr, pdelim) - 1)
   GetXSec.MidS = Mid(pStr, InStr(pStr, pdelim), Len(pdelim))
   GetXSec.RightS = Mid(pStr, InStr(pStr, pdelim) + Len(pdelim))
   GetXSec.Total = GetXSec.LeftS + GetXSec.MidS + GetXSec.RightS
End Function

Function yReplaceIt(ByVal ypstr As String, ParamArray varmyvals() As Variant) As String
'*******************************************
'Purpose:   Replace multiple characters or
'           words in a string applying Allen
'           Browne's discussion of user-
'           defined types at
'           [URL unfurl="true"]http://allenbrowne.com/ser-16.html[/URL]
'Coded by:  raskew
'Inputs:    ? yreplaceit("The quick brown fox jumped over the dog", "quick", "muddy","fox", "stream", "jumped", "plunged", "dog", "waterfall")
'Output:    The muddy brown stream plunged over the waterfall
'*******************************************

Dim MyStr As String
Dim i     As Integer
Dim idx   As Long

    MyStr = ypstr
    i = UBound(varmyvals)
    For idx = 0 To UBound(varmyvals()) Step 2
       Do While InStr(MyStr, varmyvals(idx)) > 0
          MyStr = GetXSec(MyStr, varmyvals(idx)).LeftS + varmyvals(idx + 1) + GetXSec(MyStr, varmyvals(idx)).RightS
       Loop
    Next idx
    yReplaceIt = MyStr

End Function