Re: Use table in Replace function

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" <mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A095A2DC-A3FE-4254-959D-ACD7ECDDD17E@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> 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" <mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:5B64DBCB-7710-4CDA-A281-8267C797ECE2@xxxxxxxxxxxxxxxx
>> > 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" wrote:
>> >
>> >> Hi Mike,
>> >> Thanks for responding - Access 2000
>> >>
>> >> SusanV
>> >>
>> >> "mike" <mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:2DF8ED5C-71D8-4B98-83B2-005B496A14CF@xxxxxxxxxxxxxxxx
>> >> > Susan
>> >> >
>> >> > Which version of Access are you using?
>> >> >
>> >> > "SusanV" wrote:
>> >> >
>> >> >> Background:
>> >> >> I have several forms using the replace function to clean up a
>> >> >> records
>> >> >> pulled
>> >> >> from linked table in an ancient dBase III system. Basically I'm
>> >> >> cleaning
>> >> >> up
>> >> >> /x0D and other formatting characters, and changing from uppercase
>> >> >> to
>> >> >> propercase. Because I can't modify the original data (it's actually
>> >> >> still
>> >> >> in
>> >> >> use and is modified / added to frequently), I have to make these
>> >> >> changes
>> >> >> "on
>> >> >> the fly" and insert the records into a temporary table in my
>> >> >> database,
>> >> >> then
>> >> >> users make whatever changes they are requesting to the data, then
>> >> >> output
>> >> >> to
>> >> >> text files both original and modification, then append the
>> >> >> modification
>> >> >> to a
>> >> >> permanent table for historical purposes. Works just fine, except
>> >> >> that
>> >> >> in
>> >> >> bringing the memo field from upper to lower changes acronyms that
>> >> >> should
>> >> >> remain uppercase. So I'm using the Replace function to change the
>> >> >> acronyms
>> >> >> back to upper.
>> >> >>
>> >> >> As you can imagine this is getting to be quite a long list.
>> >> >> Additionally,
>> >> >> there are several different forms that use the same list of
>> >> >> acronyms
>> >> >> to
>> >> >> be
>> >> >> converted back to uppercase. What I'd like to do is to put all
>> >> >> these
>> >> >> acronyms in a table with 2 fields:
>> >> >>
>> >> >> tblAcronyms
>> >> >> fldAcID - PK
>> >> >> fldLower
>> >> >> fldUpper
>> >> >>
>> >> >> Then have the subs use the table to perform the acronym to upper
>> >> >> changes.
>> >> >> I
>> >> >> know I need to use a recordset and a loop, but I'm not at all sure
>> >> >> how
>> >> >> to
>> >> >> go
>> >> >> about this part. Also, this list shouldn't be hard coded, so the
>> >> >> next
>> >> >> step
>> >> >> after getting this functionality in place, I'll give the users a
>> >> >> button
>> >> >> to
>> >> >> give them the ability to add acronyms to the table so that I don't
>> >> >> have
>> >> >> to
>> >> >> keep going in and adding to the code. That part I know how to do. I
>> >> >> just
>> >> >> can't seem to find how to get the loop part going...
>> >> >>
>> >> >> Below is a portion of the code I'm using. the variable Narr1 is a
>> >> >> string,
>> >> >> set to the memo field of the temp table:
>> >> >> ''''''''''''''''''''''''''''''''
>> >> >> narr1 = Replace(narr1, " ", " ")
>> >> >> narr1 = Replace(narr1, "\x0A", "")
>> >> >> narr1 = Replace(narr1, "\x0D", " " & Chr(13) & Chr(10))
>> >> >> 'Change from upper to lower case
>> >> >> narr1 = LCase([narr1])
>> >> >> 'Fix acronyms
>> >> >> narr1 = Replace([narr1], " msc ", " MSC ")
>> >> >> narr1 = Replace([narr1], " navy ", " NAVY ")
>> >> >> narr1 = Replace([narr1], " us ", " US ")
>> >> >> narr1 = Replace([narr1], " mcode", " MCode")
>> >> >> narr1 = Replace([narr1], "note:", "NOTE:")
>> >> >> narr1 = Replace([narr1], "note :", "NOTE:")
>> >> >> narr1 = Replace([narr1], "t001", "T001")
>> >> >> narr1 = Replace([narr1], " psi", " PSI")
>> >> >> ''''''''''''''''''''''''''''''''
>> >> >>
>> >> >> I'm sure this isn't all that difficult, it's just beyond me at this
>> >> >> point.
>> >> >> Always learning!!!
>> >> >>
>> >> >> TIA for any suggestions, links etc.
>> >> >>
>> >> >> SusanV
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.