Dictionary table for Replace function

S

SusanV

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
 
G

Guest

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 said:
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
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
M

Marshall Barton

SusanV said:
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")
''''''''''''''''''''''''''''''''
Click to expand...


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
 
S

SusanV

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 said:
SusanV said:
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")
''''''''''''''''''''''''''''''''
Click to expand...


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
Click to expand...
 
M

Marshall Barton

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]

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

SusanV said:
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")
''''''''''''''''''''''''''''''''
Click to expand...

"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
Click to expand...
Click to expand...
 
S

SusanV

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 said:
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]

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use table in Replace function 10
VBA Code for Replace function 2
SQL or VB find and replace table records? 3
Find/Replace function in tables 1
Need help coding a function to define relationship 2
Selecting a cell value in VB for a Query Table 1
Direction for deleting records stored in two tables 5
Get input from spreadsheet control to server for processing 1

Top