Re: Use table in Replace function

Ok. I'm assuming in your table that fldLower is the string you want to
replace, and fldUpper is what you're replacing it with. If that's the case,
the following code should work. Replace everything after 'Fix acronyms with
this:


****************************************
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblAcronyms", CurrentProject.Connection
rst.MoveFirst
Do While Not rst.EOF
narr1 = Replace(narr1, rst.Fields("fldLower"), rst.Fields("fldUpper"))
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
**********************************

I haven't tested this, but it should work, or at least get you going in the
right direction.


"SusanV" wrote:

> Hi Mike,
> Thanks for responding - Access 2000
>
> SusanV
>
> "mike" <mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:2DF8ED5C-71D8-4B98-83B2-005B496A14CF@xxxxxxxxxxxxxxxx
> > Susan
> >
> > Which version of Access are you using?
> >
> > "SusanV" wrote:
> >
> >> Background:
> >> I have several forms using the replace function to clean up a records
> >> pulled
> >> from linked table in an ancient dBase III system. Basically I'm cleaning
> >> up
> >> /x0D and other formatting characters, and changing from uppercase to
> >> propercase. Because I can't modify the original data (it's actually still
> >> in
> >> use and is modified / added to frequently), I have to make these changes
> >> "on
> >> the fly" and insert the records into a temporary table in my database,
> >> then
> >> users make whatever changes they are requesting to the data, then output
> >> to
> >> text files both original and modification, then append the modification
> >> to a
> >> permanent table for historical purposes. Works just fine, except that in
> >> bringing the memo field from upper to lower changes acronyms that should
> >> remain uppercase. So I'm using the Replace function to change the
> >> acronyms
> >> back to upper.
> >>
> >> As you can imagine this is getting to be quite a long list. Additionally,
> >> there are several different forms that use the same list of acronyms to
> >> be
> >> converted back to uppercase. What I'd like to do is to put all these
> >> acronyms in a table with 2 fields:
> >>
> >> tblAcronyms
> >> fldAcID - PK
> >> fldLower
> >> fldUpper
> >>
> >> Then have the subs use the table to perform the acronym to upper changes.
> >> I
> >> know I need to use a recordset and a loop, but I'm not at all sure how to
> >> go
> >> about this part. Also, this list shouldn't be hard coded, so the next
> >> step
> >> after getting this functionality in place, I'll give the users a button
> >> to
> >> give them the ability to add acronyms to the table so that I don't have
> >> to
> >> keep going in and adding to the code. That part I know how to do. I just
> >> can't seem to find how to get the loop part going...
> >>
> >> Below is a portion of the code I'm using. the variable Narr1 is a string,
> >> set to the memo field of the temp table:
> >> ''''''''''''''''''''''''''''''''
> >> narr1 = Replace(narr1, " ", " ")
> >> narr1 = Replace(narr1, "\x0A", "")
> >> narr1 = Replace(narr1, "\x0D", " " & Chr(13) & Chr(10))
> >> 'Change from upper to lower case
> >> narr1 = LCase([narr1])
> >> 'Fix acronyms
> >> narr1 = Replace([narr1], " msc ", " MSC ")
> >> narr1 = Replace([narr1], " navy ", " NAVY ")
> >> narr1 = Replace([narr1], " us ", " US ")
> >> narr1 = Replace([narr1], " mcode", " MCode")
> >> narr1 = Replace([narr1], "note:", "NOTE:")
> >> narr1 = Replace([narr1], "note :", "NOTE:")
> >> narr1 = Replace([narr1], "t001", "T001")
> >> narr1 = Replace([narr1], " psi", " PSI")
> >> ''''''''''''''''''''''''''''''''
> >>
> >> I'm sure this isn't all that difficult, it's just beyond me at this
> >> point.
> >> Always learning!!!
> >>
> >> TIA for any suggestions, links etc.
> >>
> >> SusanV
> >>
> >>
> >>
> >>
> >>
> >>
>
>
>
.