Use table in 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
 
S

SusanV

Hi Mike,
Thanks for responding - Access 2000

SusanV

mike said:
Susan

Which version of Access are you using?

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

mike

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 said:
Hi Mike,
Thanks for responding - Access 2000

SusanV

mike said:
Susan

Which version of Access are you using?

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

SusanV

Hi Mike,

Ok, I created teh tblAcronyms, and popped in your code, changing rst to rst4
(rst already declared). I had to go back to an earlier version of the code
as I was getting the recordsets all confused and after running once it was
telling me rst2 was not open etc. So this code has only the single recordset
previously declared in order to get the records from the old dBase III
linked table.

Now it compiles and runs without error but doesn't actually replace
anything. The SQL Update stements with the replace funtion work, but not the
loop. And I can't use the damn SQL Update Replace because I have a problem
machine that won't take the replace when enclosed in the SQL. Gah.

Here's the entire sub if you feel like picking (your code is not indented
for easier viewing):

Private Sub btnGo_Click()
' Check if revision exists

Dim con As ADODB.Connection
Dim rst As New ADODB.Recordset
Set cnx = CurrentProject.Connection

Dim SQL As String
SQL = "SELECT [MCode] FROM MCodeRewrites WHERE MCode='" & Me!Code & "';"

rst.Index = MCODE
rst.Open SQL, cnx, adOpenKeyset, adLockOptimistic, adCmdText

Debug.Print rst.RecordCount
Debug.Print rst.EOF

If rst.RecordCount = 0 Then
'Start with original MCode using frmReviseMCode
' Get record to edit
DoCmd.SetWarnings False
'Clear tblMCodeTEMP
DoCmd.RunSQL "Delete from tblMCodeTEMP", -1
'Populate tblMCodeTemp based on user input
DoCmd.RunSQL "INSERT INTO tblMCodeTEMP ( MCode, MCodeTitle, EstHrs,
MCauseCode, Narr) " _
& "SELECT MCode.MCode , MCode.MCodeTitle, MCode.ESTHRS,
MCode.MCauseCode, MCode.NARR " _
& "FROM MCode WHERE MCode = Forms!frmChooseMCode.Code;"

' Start Reformat Narrative
'''''''''''''''''''''''''''''''''''''''''
DoCmd.SetWarnings False
'Change double spaces to single spaces
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],' ',' ')"
'Remove \x0A and replace with space
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],'\x0A','')"
'Remove \x0D and replace with CRLF
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],'\x0D',' ' &
Chr(13) & Chr(10))"
'''''''''''''''''''''''''''
'Change from upper to lower case
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = LCase([NARR])"
'Fix acronyms
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim rst4 As ADODB.Recordset
Set rst4 = New ADODB.Recordset
rst4.Open "SELECT * FROM tblAcronyms", CurrentProject.Connection
rst4.MoveFirst
Do While Not rst4.EOF
narr1 = Replace(narr1, rst4.Fields("fldLower"), rst4.Fields("fldUpper"))
rst4.MoveNext
Loop
rst4.Close
Set rst4 = Nothing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Change to Proper
Dim Array1
Dim n As Integer
Dim m As Integer

Array1 = Array("0 ", "1 ", "2 ", "3 ", "4 ", "5 ", "6 ", "7 ", "8 ", "9
", ". ", ": ")
DoCmd.Hourglass True
For n = 0 To 11 '12
For m = 1 To 26

DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR]," & Chr(34)
& Array1(n) & Left(Chr(m + 96), 1) & Chr(34) & ", " & Chr(34) & Array1(n) &
Left(Chr(m + 64), 1) & Chr(34) & ")"
Next m
Next n

DoCmd.Hourglass False
DoCmd.SetWarnings True
''''''''''''''''''''''''''''''''''''''''''''''''
'Open form to edit Title or Narrative
DoCmd.OpenForm "frmReviseMCode", acNormal, "", "", acEdit, acNormal
Else: DoCmd.OpenForm "frmMCodeOpt", acNormal, "", "", acEdit, acWindowNormal
End If

rst.Close
Set rst = Nothing
cnx.Close

End Sub


mike said:
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 said:
Hi Mike,
Thanks for responding - Access 2000

SusanV

mike said:
Susan

Which version of Access are you using?

:

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


Susan, check your other threads on this question.

In the future, please don't multipost a question. If you
are unsure which group to post to, crosspost it by placing
both newsgroups, separated by a comma, in the message's
"NewsGroups:" box.
 
S

SusanV

I originally posted this to the modulesdaovb forum, but still have not seen
even my original post in that newsgroup. Don't know why, but sometimes OE
doesn't seem to pull all the messages. Otherwise I would not have
cross-posted - I do know better but thought perhaps the post had gotten lost
or misdirected.

Meantime I'll reset the local file to see whether I can get the thread.
Thanks for letting me know it DID post, and sorry!

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


Susan, check your other threads on this question.

In the future, please don't multipost a question. If you
are unsure which group to post to, crosspost it by placing
both newsgroups, separated by a comma, in the message's
"NewsGroups:" box.
Click to expand...
 
S

SusanV

Got that thread, and thanks, still no go. <sigh>


SusanV said:
I originally posted this to the modulesdaovb forum, but still have not seen
even my original post in that newsgroup. Don't know why, but sometimes OE
doesn't seem to pull all the messages. Otherwise I would not have
cross-posted - I do know better but thought perhaps the post had gotten
lost or misdirected.

Meantime I'll reset the local file to see whether I can get the thread.
Thanks for letting me know it DID post, and sorry!

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


Susan, check your other threads on this question.

In the future, please don't multipost a question. If you
are unsure which group to post to, crosspost it by placing
both newsgroups, separated by a comma, in the message's
"NewsGroups:" box.
Click to expand...
Click to expand...
 
M

mike

Susan,

I don't have enough time to parse your entire sub procedure, but after a
quick scan I think the problem is that I didn't put my Replace() code inside
an UPDATE statement. In my original code, try replacing the line narr1 = ...
with the following:

DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],'" &
rst4.Fields("fldLower") & "','" & rst4.Fields("fldUpper") & "')"

If you're still getting errors, change the DoCmd.RunSQL in the above line to
Debug.Print. That will cause the SQL statement to be printed out to the
immediate window, where you can inspect the syntax. Confirm that the fields
from tblAcronym are coming in correctly, and that they've been properly
encapsulated in single quotes. While debugging, you may also want to put in a
break point, so that the entire loop won't run causing all the SQL statements
to get printed out.

Good luck. Hopefully this will work for you.

SusanV said:
Hi Mike,

Ok, I created teh tblAcronyms, and popped in your code, changing rst to rst4
(rst already declared). I had to go back to an earlier version of the code
as I was getting the recordsets all confused and after running once it was
telling me rst2 was not open etc. So this code has only the single recordset
previously declared in order to get the records from the old dBase III
linked table.

Now it compiles and runs without error but doesn't actually replace
anything. The SQL Update stements with the replace funtion work, but not the
loop. And I can't use the damn SQL Update Replace because I have a problem
machine that won't take the replace when enclosed in the SQL. Gah.

Here's the entire sub if you feel like picking (your code is not indented
for easier viewing):

Private Sub btnGo_Click()
' Check if revision exists

Dim con As ADODB.Connection
Dim rst As New ADODB.Recordset
Set cnx = CurrentProject.Connection

Dim SQL As String
SQL = "SELECT [MCode] FROM MCodeRewrites WHERE MCode='" & Me!Code & "';"

rst.Index = MCODE
rst.Open SQL, cnx, adOpenKeyset, adLockOptimistic, adCmdText

Debug.Print rst.RecordCount
Debug.Print rst.EOF

If rst.RecordCount = 0 Then
'Start with original MCode using frmReviseMCode
' Get record to edit
DoCmd.SetWarnings False
'Clear tblMCodeTEMP
DoCmd.RunSQL "Delete from tblMCodeTEMP", -1
'Populate tblMCodeTemp based on user input
DoCmd.RunSQL "INSERT INTO tblMCodeTEMP ( MCode, MCodeTitle, EstHrs,
MCauseCode, Narr) " _
& "SELECT MCode.MCode , MCode.MCodeTitle, MCode.ESTHRS,
MCode.MCauseCode, MCode.NARR " _
& "FROM MCode WHERE MCode = Forms!frmChooseMCode.Code;"

' Start Reformat Narrative
'''''''''''''''''''''''''''''''''''''''''
DoCmd.SetWarnings False
'Change double spaces to single spaces
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],' ',' ')"
'Remove \x0A and replace with space
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],'\x0A','')"
'Remove \x0D and replace with CRLF
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],'\x0D',' ' &
Chr(13) & Chr(10))"
'''''''''''''''''''''''''''
'Change from upper to lower case
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = LCase([NARR])"
'Fix acronyms
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim rst4 As ADODB.Recordset
Set rst4 = New ADODB.Recordset
rst4.Open "SELECT * FROM tblAcronyms", CurrentProject.Connection
rst4.MoveFirst
Do While Not rst4.EOF
narr1 = Replace(narr1, rst4.Fields("fldLower"), rst4.Fields("fldUpper"))
rst4.MoveNext
Loop
rst4.Close
Set rst4 = Nothing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Change to Proper
Dim Array1
Dim n As Integer
Dim m As Integer

Array1 = Array("0 ", "1 ", "2 ", "3 ", "4 ", "5 ", "6 ", "7 ", "8 ", "9
", ". ", ": ")
DoCmd.Hourglass True
For n = 0 To 11 '12
For m = 1 To 26

DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR]," & Chr(34)
& Array1(n) & Left(Chr(m + 96), 1) & Chr(34) & ", " & Chr(34) & Array1(n) &
Left(Chr(m + 64), 1) & Chr(34) & ")"
Next m
Next n

DoCmd.Hourglass False
DoCmd.SetWarnings True
''''''''''''''''''''''''''''''''''''''''''''''''
'Open form to edit Title or Narrative
DoCmd.OpenForm "frmReviseMCode", acNormal, "", "", acEdit, acNormal
Else: DoCmd.OpenForm "frmMCodeOpt", acNormal, "", "", acEdit, acWindowNormal
End If

rst.Close
Set rst = Nothing
cnx.Close

End Sub


mike said:
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 said:
Hi Mike,
Thanks for responding - Access 2000

SusanV

Susan

Which version of Access are you using?

:

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

SusanV

Thanks Mike, and I understand about not having time - i really DO appreciate
teh time you have put in! However, I can't use the Replace inside an Update
as i have one problem workstation that will only run the Replace function
outside of SQL. (If your curious see the thread "Undefined Function
'Replace'" in the microsoft.public.access ng. Another nightmare)

I'll keep plugging at this, I'm going to pull some tables to a smaller dev
db and work this out with a simpler sub, then bring it into the more complex
sub btnGo in my actual db. And no, I haven't been playing in the live db, a
copy of cours! <grin>

Again, many thanks gfor your time and effort,

SusanV

mike said:
Susan,

I don't have enough time to parse your entire sub procedure, but after a
quick scan I think the problem is that I didn't put my Replace() code
inside
an UPDATE statement. In my original code, try replacing the line narr1 =
...
with the following:

DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],'" &
rst4.Fields("fldLower") & "','" & rst4.Fields("fldUpper") & "')"

If you're still getting errors, change the DoCmd.RunSQL in the above line
to
Debug.Print. That will cause the SQL statement to be printed out to the
immediate window, where you can inspect the syntax. Confirm that the
fields
from tblAcronym are coming in correctly, and that they've been properly
encapsulated in single quotes. While debugging, you may also want to put
in a
break point, so that the entire loop won't run causing all the SQL
statements
to get printed out.

Good luck. Hopefully this will work for you.

SusanV said:
Hi Mike,

Ok, I created teh tblAcronyms, and popped in your code, changing rst to
rst4
(rst already declared). I had to go back to an earlier version of the
code
as I was getting the recordsets all confused and after running once it
was
telling me rst2 was not open etc. So this code has only the single
recordset
previously declared in order to get the records from the old dBase III
linked table.

Now it compiles and runs without error but doesn't actually replace
anything. The SQL Update stements with the replace funtion work, but not
the
loop. And I can't use the damn SQL Update Replace because I have a
problem
machine that won't take the replace when enclosed in the SQL. Gah.

Here's the entire sub if you feel like picking (your code is not indented
for easier viewing):

Private Sub btnGo_Click()
' Check if revision exists

Dim con As ADODB.Connection
Dim rst As New ADODB.Recordset
Set cnx = CurrentProject.Connection

Dim SQL As String
SQL = "SELECT [MCode] FROM MCodeRewrites WHERE MCode='" & Me!Code & "';"

rst.Index = MCODE
rst.Open SQL, cnx, adOpenKeyset, adLockOptimistic, adCmdText

Debug.Print rst.RecordCount
Debug.Print rst.EOF

If rst.RecordCount = 0 Then
'Start with original MCode using frmReviseMCode
' Get record to edit
DoCmd.SetWarnings False
'Clear tblMCodeTEMP
DoCmd.RunSQL "Delete from tblMCodeTEMP", -1
'Populate tblMCodeTemp based on user input
DoCmd.RunSQL "INSERT INTO tblMCodeTEMP ( MCode, MCodeTitle, EstHrs,
MCauseCode, Narr) " _
& "SELECT MCode.MCode , MCode.MCodeTitle, MCode.ESTHRS,
MCode.MCauseCode, MCode.NARR " _
& "FROM MCode WHERE MCode = Forms!frmChooseMCode.Code;"

' Start Reformat Narrative
'''''''''''''''''''''''''''''''''''''''''
DoCmd.SetWarnings False
'Change double spaces to single spaces
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],' ','
')"
'Remove \x0A and replace with space
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR =
Replace([NARR],'\x0A','')"
'Remove \x0D and replace with CRLF
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],'\x0D','
' &
Chr(13) & Chr(10))"
'''''''''''''''''''''''''''
'Change from upper to lower case
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = LCase([NARR])"
'Fix acronyms
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim rst4 As ADODB.Recordset
Set rst4 = New ADODB.Recordset
rst4.Open "SELECT * FROM tblAcronyms", CurrentProject.Connection
rst4.MoveFirst
Do While Not rst4.EOF
narr1 = Replace(narr1, rst4.Fields("fldLower"),
rst4.Fields("fldUpper"))
rst4.MoveNext
Loop
rst4.Close
Set rst4 = Nothing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Change to Proper
Dim Array1
Dim n As Integer
Dim m As Integer

Array1 = Array("0 ", "1 ", "2 ", "3 ", "4 ", "5 ", "6 ", "7 ", "8 ",
"9
", ". ", ": ")
DoCmd.Hourglass True
For n = 0 To 11 '12
For m = 1 To 26

DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR]," &
Chr(34)
& Array1(n) & Left(Chr(m + 96), 1) & Chr(34) & ", " & Chr(34) & Array1(n)
&
Left(Chr(m + 64), 1) & Chr(34) & ")"
Next m
Next n

DoCmd.Hourglass False
DoCmd.SetWarnings True
''''''''''''''''''''''''''''''''''''''''''''''''
'Open form to edit Title or Narrative
DoCmd.OpenForm "frmReviseMCode", acNormal, "", "", acEdit, acNormal
Else: DoCmd.OpenForm "frmMCodeOpt", acNormal, "", "", acEdit,
acWindowNormal
End If

rst.Close
Set rst = Nothing
cnx.Close

End Sub


mike said:
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.


:

Hi Mike,
Thanks for responding - Access 2000

SusanV

Susan

Which version of Access are you using?

:

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

SusanV

Hi Mike,

I blew away my existing code and rewrote it from scratch bringing in your
'fix acronyms'section and it works like a charm. Not sure what was kaflooey
and don't want to take the time to dig, but I DID want to post back thanking
you for all your help.

Much appreciated!!

Regards,
SusanV

mike said:
Susan,

I don't have enough time to parse your entire sub procedure, but after a
quick scan I think the problem is that I didn't put my Replace() code
inside
an UPDATE statement. In my original code, try replacing the line narr1 =
...
with the following:

DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],'" &
rst4.Fields("fldLower") & "','" & rst4.Fields("fldUpper") & "')"

If you're still getting errors, change the DoCmd.RunSQL in the above line
to
Debug.Print. That will cause the SQL statement to be printed out to the
immediate window, where you can inspect the syntax. Confirm that the
fields
from tblAcronym are coming in correctly, and that they've been properly
encapsulated in single quotes. While debugging, you may also want to put
in a
break point, so that the entire loop won't run causing all the SQL
statements
to get printed out.

Good luck. Hopefully this will work for you.

SusanV said:
Hi Mike,

Ok, I created teh tblAcronyms, and popped in your code, changing rst to
rst4
(rst already declared). I had to go back to an earlier version of the
code
as I was getting the recordsets all confused and after running once it
was
telling me rst2 was not open etc. So this code has only the single
recordset
previously declared in order to get the records from the old dBase III
linked table.

Now it compiles and runs without error but doesn't actually replace
anything. The SQL Update stements with the replace funtion work, but not
the
loop. And I can't use the damn SQL Update Replace because I have a
problem
machine that won't take the replace when enclosed in the SQL. Gah.

Here's the entire sub if you feel like picking (your code is not indented
for easier viewing):

Private Sub btnGo_Click()
' Check if revision exists

Dim con As ADODB.Connection
Dim rst As New ADODB.Recordset
Set cnx = CurrentProject.Connection

Dim SQL As String
SQL = "SELECT [MCode] FROM MCodeRewrites WHERE MCode='" & Me!Code & "';"

rst.Index = MCODE
rst.Open SQL, cnx, adOpenKeyset, adLockOptimistic, adCmdText

Debug.Print rst.RecordCount
Debug.Print rst.EOF

If rst.RecordCount = 0 Then
'Start with original MCode using frmReviseMCode
' Get record to edit
DoCmd.SetWarnings False
'Clear tblMCodeTEMP
DoCmd.RunSQL "Delete from tblMCodeTEMP", -1
'Populate tblMCodeTemp based on user input
DoCmd.RunSQL "INSERT INTO tblMCodeTEMP ( MCode, MCodeTitle, EstHrs,
MCauseCode, Narr) " _
& "SELECT MCode.MCode , MCode.MCodeTitle, MCode.ESTHRS,
MCode.MCauseCode, MCode.NARR " _
& "FROM MCode WHERE MCode = Forms!frmChooseMCode.Code;"

' Start Reformat Narrative
'''''''''''''''''''''''''''''''''''''''''
DoCmd.SetWarnings False
'Change double spaces to single spaces
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],' ','
')"
'Remove \x0A and replace with space
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR =
Replace([NARR],'\x0A','')"
'Remove \x0D and replace with CRLF
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR],'\x0D','
' &
Chr(13) & Chr(10))"
'''''''''''''''''''''''''''
'Change from upper to lower case
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = LCase([NARR])"
'Fix acronyms
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim rst4 As ADODB.Recordset
Set rst4 = New ADODB.Recordset
rst4.Open "SELECT * FROM tblAcronyms", CurrentProject.Connection
rst4.MoveFirst
Do While Not rst4.EOF
narr1 = Replace(narr1, rst4.Fields("fldLower"),
rst4.Fields("fldUpper"))
rst4.MoveNext
Loop
rst4.Close
Set rst4 = Nothing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Change to Proper
Dim Array1
Dim n As Integer
Dim m As Integer

Array1 = Array("0 ", "1 ", "2 ", "3 ", "4 ", "5 ", "6 ", "7 ", "8 ",
"9
", ". ", ": ")
DoCmd.Hourglass True
For n = 0 To 11 '12
For m = 1 To 26

DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = Replace([NARR]," &
Chr(34)
& Array1(n) & Left(Chr(m + 96), 1) & Chr(34) & ", " & Chr(34) & Array1(n)
&
Left(Chr(m + 64), 1) & Chr(34) & ")"
Next m
Next n

DoCmd.Hourglass False
DoCmd.SetWarnings True
''''''''''''''''''''''''''''''''''''''''''''''''
'Open form to edit Title or Narrative
DoCmd.OpenForm "frmReviseMCode", acNormal, "", "", acEdit, acNormal
Else: DoCmd.OpenForm "frmMCodeOpt", acNormal, "", "", acEdit,
acWindowNormal
End If

rst.Close
Set rst = Nothing
cnx.Close

End Sub


mike said:
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.


:

Hi Mike,
Thanks for responding - Access 2000

SusanV

Susan

Which version of Access are you using?

:

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

Dictionary table for Replace function 6
Capitalisation in Find and Replace operations 9
Replace Data In A Table 1
Wildcard in the Replace function 2
Returning the month (mmm) from cell in date format dd/mm/yyyy 3
replace characters in table 6
Replace first character of name in formfield (Instr function) 4
Case preservation in replace 6

Top