Find and Replace against set of rules in 2nd table

[Hank Rouse]

Looking for a tool, or code that does the following.

Edit [partial field data] in TBL1, if [partial field data] meets criteria in
TBL2, COL1. TBL2, COL2 has the REPLACE WITH information.

Not sure if this edited data can be written directly to the TBL1, or needs
to write to a TEMP TBL. (Would prefer an Update Query)

As I will continue to be tweaking and TBL2 COL1 & COL2, I would like this to
be a query so that I can run it as needed.

Thanks in Advance

 

[TC]

This is more like an editor or wordprocessor "find and replace"
function. Why are you trying to do this in a database program? What are
you trying to achieve from an "end user" viewpoint?

TC

 

[Hank Rouse]

I presently have 59 criteria for Find and Replace, and the list is sure to
grow. Attempting to consolidate our different satellite offices into one
general reporting medium.

 

[TC]

Wow, that was quick!

You still haven't really explained what you're trying to do. I
understand that you want some kind of "find and replace" functionality.
But that functionality is unusual to want, in a database program like
Micosoft Access. I suspect that you may be trying to do something the
"wrong way". I need to know, what is that "something"!

Perhaps give me a (small) real example, including real data?

Cheers,
TC

 

[Hank Rouse]

Sorry, I'll go more into depth.

Each of our satelite offices have been putting together their own reports
and sending them into headquarters. I'm setting up a weekly text file data
dump from the satelite offices to a file here at corporate which I import
into Excel, play with a bit, then dump into Access.

Eventually it gets spit out via Crystal Reports, and finally were all on the
same page, looking at the same report.

Presently, the 59 criteria are things like address, formatting issues,
spelling issues. Systems presently inplace are very antiquated. I have to
prove ROI via the reports, before I can get the funds to replace the old
systems and go with something better. Old systems are set, that after data
entry, record is locked. Must close account, and create a new one.

 

[TC]

Sorry, I still don't get it. If the changes that you need to do, relate
to "addresses, formatting issues, spelling issues", and so on, you
should be doing that in a wordprocessing program like Word. It is
possible, but quite inappropriate, imho, to try to do such things in a
database product like Access. It's like trying to hammer a nail, with
the butt end of a screwdriver. Sure you can do it, but it's inefficient
& will wreck the screwdriver; bettrer to use a hammer instead!

Remember you can automate find & replaces in Word, using VBA /in Word/.
In addition, Word has a recording feature which records the VBA (code)
equivalent of your manual actions. So you could do some finds &
replaces, of the kinds that you wanted to do, then see what VBA was
generated for those. Then you could use that VBA in your own code - /in
Word/. Word VBA has lots of things that would be pertinent to your need
& which /are not/ in Access VBA. For example, Word VBA has a built-in
find/replace function - Access VBA does not.

Sorry, but absent any further information, I can't see any
justification for doing your finds & replaces in Access. I recommend
that you post your requirements to a Word newsgroup & ask for uidance
there. I'm sure you'd get a better result, at the end of the day, by
using Word for its intended purpose - not Access for a non-intended
one!

Cheers,
TC

 

[TC]

PS. I'm not saying don't use Access /at all/. I'm just saying, don't
use it for nontasks that would be better done in Word.

HTH,
TC

 

[TC]

"tasks", not "nontasks"

TC

 

[Hank Rouse]

Because the text files are comma delimited in the first place, Word messes
everything up.

Can you at least point me to a place to do this in Excel.

59 criteria x 19 sites (each with their own file) Can become a very
daunting task.

My biggest concern is that the criteria will grow, and I need an easy way to
manage the criteria, as time moves forward. Could easily go above 200 in
the next few months.

Was planning on building a report in the future, that detailed the
occurrences of each criteria, so that I could eliminate them as each is
resolved across each of the 19 sites.

 

[Hank Rouse]

TBL1

COL1
Mari Johnson
Jim Evans
Charles Stanley
Jerry Straight

TBL2

COL1 COL2
Mari Mary
Straight Strait

See, I'm simply searching TBL1, COL1 for any match in TBL2, COL1.
If a match is found, Bad data in TBL1, COL1 is replaced by TBL2, COL2

 

[TC]

Sorry, I hit my posting limit & couldn't reply.

Hank, your example confirms me in my previous view: this is a task for
VBA code in MS Word, not VBA code in MS Access. MS Word VBA already has
methods for find & replace.

On the other hand, if you know some Access VBA, and you don't know any
Word VBA, I accept that it could be a difficult judgement call. So
here's some code to help you get started. This is off the top of my
head & therefore UNTESTED:

dim db as database, rs1 as recordset, rs2 as recordset
dim sOld as string, sNew as string, sCurrent as string, n as integer

set db = currentdb()
set rs1 = db.openrecordset ("TBL1")
set rs2 = db.openrecordset ("TBL2")

while not rs2.eof

' get next old/new value pair.
sOld = rs2![COL1]
sNew = rs2![COL2]

' for each record in TBL1:
rs1.movefirst
while not rs1.eof

' get current value of field.
sCurrent = rs1![COL1]

' replace any & all occurrences of sOld, with sNew.
n = instr (sCurrent, sOld)
while n > 0
sCurrent = left$(sCurrent, n-1) & sNew & mid$(sCurrent,
n+len(sOld))
n = instr (sCurrent, sOld)
wend

' assume something changed, update the field.
rs1.edit
rs1![COL1] = sCurrent
rs1.update

' go to next record in TBL1.
rs1.bookmark = rs1.lastupdated ' <?? check the method name.
rs1.movenext

wend

' try next old/new pair.
rs2.movenext

wend

set rs1 = nothing
set rs2 = nothing
set db = nothing

or somesuch!

HTH,
TC

 

[Hank Rouse]

I believe were getting close.

Keep getting
Compile error:
Invalid outside procedure

Using VB6.3.8863, very new to VB sorry

Here is the modified code

Option Compare Database

Dim db As database, rs1 As Recordset, rs2 As Recordset
Dim sOld As String, sNew As String, sCurrent As String, n As Integer

Set db = CurrentDb()
Set rs1 = db.openrecordset("TEMP_IMPORT")
Set rs2 = db.openrecordset("AddressConversion")

While Not rs2.EOF

' get next old/new value pair.
sOld = rs2![If]
sNew = rs2![Change_To]

' for each record in TBL1:
rs1.MoveFirst
While Not rs1.EOF

' get current value of field.
sCurrent = rs1![email_address]

' replace any & all occurrences of sOld, with sNew.
n = InStr(sCurrent, sOld)
While n > 0
sCurrent = left$(sCurrent, n-1) & sNew & mid$(sCurrent,
n+len(sOld))
n = InStr(sCurrent, sOld)
Wend

' assume something changed, update the field.
rs1.edit
rs1![COL1] = sCurrent
rs1.Update

' go to next record in TBL1.
rs1.Bookmark = rs1.lastupdated ' <?? check the method name.
rs1.MoveNext

Wend

' try next old/new pair.
rs2.MoveNext

Wend

Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

 

[TC]

You just need to put the code inside a procedure:

Option Explicit
Option Compare Database
public sub DoIt()
( -- code goes here -- )
end sub

Then, to run that code, press Ctrl-g to open the debug (or "immediate")
window, then type "doit" (without the quotes) and press Enter.

Of course, you'd probably want to call the code from a command button
on a form, or whatever. But the above is all that you need to do, to
get it running & see if it will meet your needs.

HTH,
TC

 

[TC]

Hank, please do a web search on the terms MULTI POSTING and CROSS
POSTING.

Multiposting is BAD, and will surely make everyone MAD.

HTH,
TC