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

Hi Susan,

It should be fairly simple to modify your code to use a table as you have
suggested.

Following is an example (air code) using DAO.

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim (your other variables)...

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
Set db = Currentdb
Set rst = db.OpenRecordset("tblAcronyms",dbOpenSnapshot)
With rst
Do While Not .eof
narr1 = Replace([narr1], !fldLower, !fldUpper)
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set db = Nothing

'Finish any remaining code

By the way, you could probably move your other replace criteria to the table
as well (the ones currently above the loop), except possibly for the one
containing the line break (although you may be able to try making the second
field a memo and entering a line break manually, but I'm not sure if that
would work or not)

HTH, Ted Allen
"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
>
>
>
>
>

Oops, just noticed that the Openrecordset part of the code may have wrapped
funny (at least it reads funny in my viewer). The values in the ()'s
following the openrecordset should be immediately after the word
OpenRecordset on the same line.

"Ted Allen" wrote:

> Hi Susan,
>
> It should be fairly simple to modify your code to use a table as you have
> suggested.
>
> Following is an example (air code) using DAO.
>
> Dim db as DAO.Database
> Dim rst as DAO.Recordset
> Dim (your other variables)...
>
> 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
> Set db = Currentdb
> Set rst = db.OpenRecordset("tblAcronyms",dbOpenSnapshot)
> With rst
> Do While Not .eof
> narr1 = Replace([narr1], !fldLower, !fldUpper)
> .MoveNext
> Loop
> .Close
> End With
>
> Set rst = Nothing
> Set db = Nothing
>
> 'Finish any remaining code
>
> By the way, you could probably move your other replace criteria to the table
> as well (the ones currently above the loop), except possibly for the one
> containing the line break (although you may be able to try making the second
> field a memo and entering a line break manually, but I'm not sure if that
> would work or not)
>
> HTH, Ted Allen
> "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
> >
> >
> >
> >
> >

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")
>''''''''''''''''''''''''''''''''


In general, this is at least a messy problem, and, at worst,
unsolvable. (e.g. " us " is a fairly common word, not just
an acronym.

However, here's some air code that I think will do what you
want, but speed will not be be good.

First, note that the double blank, \x0D, etc. can be added
to the acronyms table so they do not require special
handling.

Sim db As Database
Dim rs As RecordSet
Dim strSQL As String

narr1 = LCase(narr1)
strSQL = "SELECT fldLower, fldUpper FROM tblAcronyms " _
& "WHERE InStr(""" & Replace(narr1, """", """""") _
& """, fldLower) > 0"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
Do Until rs.EOF
narr1 = Replace(narr1, fldLower, fldUpper)
rs.MoveNext
Loop
End If
rs.Close: Set rs = Nothing

--
Marsh
MVP [MS Access]

Hi Marshall,

Thanks for helping! Using your code sample, I get a "type mismatch" error on
this line:
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

SusanV

"Marshall Barton" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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")
>>''''''''''''''''''''''''''''''''
>
>
> In general, this is at least a messy problem, and, at worst,
> unsolvable. (e.g. " us " is a fairly common word, not just
> an acronym.
>
> However, here's some air code that I think will do what you
> want, but speed will not be be good.
>
> First, note that the double blank, \x0D, etc. can be added
> to the acronyms table so they do not require special
> handling.
>
> Sim db As Database
> Dim rs As RecordSet
> Dim strSQL As String
>
> narr1 = LCase(narr1)
> strSQL = "SELECT fldLower, fldUpper FROM tblAcronyms " _
> & "WHERE InStr(""" & Replace(narr1, """", """""") _
> & """, fldLower) > 0"
> Set db = CurrentDb()
> Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
> If rs.RecordCount > 0 Then
> Do Until rs.EOF
> narr1 = Replace(narr1, fldLower, fldUpper)
> rs.MoveNext
> Loop
> End If
> rs.Close: Set rs = Nothing
>
> --
> Marsh
> MVP [MS Access]

The only thing that comes to mind is that you may be using
ADO and I posted DAO code. I think the response you got in
the other thread is logically the same, but uses ADO so, if
you are using ADO, give that a try.
--
Marsh
MVP [MS Access]


SusanV wrote:
>Thanks for helping! Using your code sample, I get a "type mismatch" error on
>this line:
>Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
>
>
>> 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")
>>>''''''''''''''''''''''''''''''''
>>
>>
>"Marshall Barton" wrote
>> In general, this is at least a messy problem, and, at worst,
>> unsolvable. (e.g. " us " is a fairly common word, not just
>> an acronym.
>>
>> However, here's some air code that I think will do what you
>> want, but speed will not be be good.
>>
>> First, note that the double blank, \x0D, etc. can be added
>> to the acronyms table so they do not require special
>> handling.
>>
>> Sim db As Database
>> Dim rs As RecordSet
>> Dim strSQL As String
>>
>> narr1 = LCase(narr1)
>> strSQL = "SELECT fldLower, fldUpper FROM tblAcronyms " _
>> & "WHERE InStr(""" & Replace(narr1, """", """""") _
>> & """, fldLower) > 0"
>> Set db = CurrentDb()
>> Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
>> If rs.RecordCount > 0 Then
>> Do Until rs.EOF
>> narr1 = Replace(narr1, fldLower, fldUpper)
>> rs.MoveNext
>> Loop
>> End If
>> rs.Close: Set rs = Nothing

Doh! My head is spinning today, too many distractions (read: L-users) while
I'm trying to learn something new... Yes, I have been using ADO, I should
have posted to the ADO group not the DAO group. Yet another blonde moment!
<grin>

Thanks for your help, I'll get this eventually!

SusanV


"Marshall Barton" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> The only thing that comes to mind is that you may be using
> ADO and I posted DAO code. I think the response you got in
> the other thread is logically the same, but uses ADO so, if
> you are using ADO, give that a try.
> --
> Marsh
> MVP [MS Access]
>
>
> SusanV wrote:
>>Thanks for helping! Using your code sample, I get a "type mismatch" error
>>on
>>this line:
>>Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
>>
>>
>>> 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")
>>>>''''''''''''''''''''''''''''''''
>>>
>>>
>>"Marshall Barton" wrote
>>> In general, this is at least a messy problem, and, at worst,
>>> unsolvable. (e.g. " us " is a fairly common word, not just
>>> an acronym.
>>>
>>> However, here's some air code that I think will do what you
>>> want, but speed will not be be good.
>>>
>>> First, note that the double blank, \x0D, etc. can be added
>>> to the acronyms table so they do not require special
>>> handling.
>>>
>>> Sim db As Database
>>> Dim rs As RecordSet
>>> Dim strSQL As String
>>>
>>> narr1 = LCase(narr1)
>>> strSQL = "SELECT fldLower, fldUpper FROM tblAcronyms " _
>>> & "WHERE InStr(""" & Replace(narr1, """", """""") _
>>> & """, fldLower) > 0"
>>> Set db = CurrentDb()
>>> Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
>>> If rs.RecordCount > 0 Then
>>> Do Until rs.EOF
>>> narr1 = Replace(narr1, fldLower, fldUpper)
>>> rs.MoveNext
>>> Loop
>>> End If
>>> rs.Close: Set rs = Nothing