RE: Use table in Replace function

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
>
>
>
>
>
>
.