Re: Use table in Replace function

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


.

Loading