Update field using String?

[SusanV]

I have data that is full of CRLF and space characters (\x0D and x\0A) that
have to be removed in a temporary holding table called TempNarr (the
original data can't be altered). I've got the Replace function working
perfectly in a button's onClick event, but can't figure what to do next to
use the modified String in an UPDATE statement for the TempNarr table. Users
don't need to see the data refreshed in the form (although that would be
nice) as there's a report already setup to use the TempNarr table as it's
source. Below is the code I have so far, any help or even a link would be
fabulous!
TIA,
Susan


Private Sub FixNarr_Click()

DoCmd.SetWarnings True
DoCmd.RunSQL "Delete from TempNarr"
DoCmd.RunSQL "INSERT INTO TempNarr(MCODE, MCODETITLE," & _
"ESTHRS, PMCODE, NARR, MCAUSECODE) SELECT MCODE.MCODE," & _
"mcode.MCODETITLE, mcode.ESTHRS, mcode.PMCODE, mcode.NARR," & _
" mcode.MCAUSECODE FROM MCode" & _
" WHERE MCODE = Forms!frmFindMCode.MCode"

Dim strOld As String
Dim strFind As String
Dim strReplace As String
Dim strNew As String
Dim strOld1 As String
Dim strFind1 As String
Dim strReplace1 As String
Dim strNew1 As String

strFind = "\x0D"
strReplace = " " & Chr(13) & Chr(10)
strOld = Forms!frmFindMCode.NARR

MsgBox ("Narr: " & strOld)

strNew = Replace(strOld, strFind, strReplace)

strFind1 = "\x0A"
strReplace1 = ""
strOld1 = strNew
strNew1 = Replace(strOld1, strFind1, strReplace1)

MsgBox ("Now Narr: " & strNew1)

End Sub

 

[Guest]

DoCmd.RunSQL "Update TempNarr Set NARR = """ & strNew1 & _
""" where MCODE = Forms!frmFindMCode.MCode"
(I'm using "" to replace " in the SQL so you don't have problems with
embedded ' (apostrophes) in you data)

That will update just the record that you added at the beginning of the
procedure.

 

[Steve Schapel]

Susan,

Try it like this...

Private Sub FixNarr_Click()
DoCmd.SetWarnings True
DoCmd.RunSQL "Delete from TempNarr"
DoCmd.RunSQL "INSERT INTO TempNarr(MCODE, MCODETITLE," & _
"ESTHRS, PMCODE, NARR, MCAUSECODE) SELECT MCODE.MCODE," & _
"mcode.MCODETITLE, mcode.ESTHRS, mcode.PMCODE, mcode.NARR," & _
" mcode.MCAUSECODE FROM MCode" & _
" WHERE MCODE = Forms!frmFindMCode.MCode"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace('[NARR]','\x0D',' '
& Chr(13) & Chr(10))"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace('[NARR]','\x0A','')"
End Sub

 

[SusanV]

Thanks for the help! I've been beating my brain in all day on this - and now
it works!!

Thanks SO MUCH for your help!!!

Susan

 

[SusanV]

Excellent! Shorter than my current solution, and welcome indeed! I can't
believe I couldn't find this anywhere on the web or in the ngs. There's more
than one way to skin a cat!

<gratefully copies code to notepad for future reference>

Thanks!!!!

Susan

Susan,

Try it like this...

Private Sub FixNarr_Click()
DoCmd.SetWarnings True
DoCmd.RunSQL "Delete from TempNarr"
DoCmd.RunSQL "INSERT INTO TempNarr(MCODE, MCODETITLE," & _
"ESTHRS, PMCODE, NARR, MCAUSECODE) SELECT MCODE.MCODE," & _
"mcode.MCODETITLE, mcode.ESTHRS, mcode.PMCODE, mcode.NARR," & _
" mcode.MCAUSECODE FROM MCode" & _
" WHERE MCODE = Forms!frmFindMCode.MCode"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace('[NARR]','\x0D',' ' &
Chr(13) & Chr(10))"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace('[NARR]','\x0A','')"
End Sub

--
Steve Schapel, Microsoft Access MVP

I have data that is full of CRLF and space characters (\x0D and x\0A)
that have to be removed in a temporary holding table called TempNarr (the
original data can't be altered). I've got the Replace function working
perfectly in a button's onClick event, but can't figure what to do next
to use the modified String in an UPDATE statement for the TempNarr table.
Users don't need to see the data refreshed in the form (although that
would be nice) as there's a report already setup to use the TempNarr
table as it's source. Below is the code I have so far, any help or even a
link would be fabulous!
TIA,
Susan


Private Sub FixNarr_Click()

DoCmd.SetWarnings True
DoCmd.RunSQL "Delete from TempNarr"
DoCmd.RunSQL "INSERT INTO TempNarr(MCODE, MCODETITLE," & _
"ESTHRS, PMCODE, NARR, MCAUSECODE) SELECT MCODE.MCODE," & _
"mcode.MCODETITLE, mcode.ESTHRS, mcode.PMCODE, mcode.NARR," & _
" mcode.MCAUSECODE FROM MCode" & _
" WHERE MCODE = Forms!frmFindMCode.MCode"

Dim strOld As String
Dim strFind As String
Dim strReplace As String
Dim strNew As String
Dim strOld1 As String
Dim strFind1 As String
Dim strReplace1 As String
Dim strNew1 As String

strFind = "\x0D"
strReplace = " " & Chr(13) & Chr(10)
strOld = Forms!frmFindMCode.NARR

MsgBox ("Narr: " & strOld)

strNew = Replace(strOld, strFind, strReplace)

strFind1 = "\x0A"
strReplace1 = ""
strOld1 = strNew
strNew1 = Replace(strOld1, strFind1, strReplace1)

MsgBox ("Now Narr: " & strNew1)

End Sub

 

[Steve Schapel]

Susan,

Well, if you're taking notes for future reference, here are a couple of
other comments...
1. I notice you have DoCmd.SetWarnings True in your code. Normally
when using DoCmdRunSQL or DoCmd.OpenQuery in code, it would be more
usual to turn the warnings off before the action queries, and on again
afterwards.
2. An alternative to DoCmd.RunSQL, which seems to be more trendy at the
moment, and does not need any SetWarnings one way or the other, is like
this...
CurrentDb.Execute "UPDATE TempNarr SET NARR =
Replace('[NARR]','\x0A','')", dbFailOnError

 

[SusanV]

Some of the records have either " or ' (or both) in the field I'm
correcting, is there any way to have the Replace function ignore these?

Thanks again,

Susan

Susan,

Try it like this...

Private Sub FixNarr_Click()
DoCmd.SetWarnings True
DoCmd.RunSQL "Delete from TempNarr"
DoCmd.RunSQL "INSERT INTO TempNarr(MCODE, MCODETITLE," & _
"ESTHRS, PMCODE, NARR, MCAUSECODE) SELECT MCODE.MCODE," & _
"mcode.MCODETITLE, mcode.ESTHRS, mcode.PMCODE, mcode.NARR," & _
" mcode.MCAUSECODE FROM MCode" & _
" WHERE MCODE = Forms!frmFindMCode.MCode"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace('[NARR]','\x0D',' ' &
Chr(13) & Chr(10))"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace('[NARR]','\x0A','')"
End Sub

--
Steve Schapel, Microsoft Access MVP

I have data that is full of CRLF and space characters (\x0D and x\0A)
that have to be removed in a temporary holding table called TempNarr (the
original data can't be altered). I've got the Replace function working
perfectly in a button's onClick event, but can't figure what to do next
to use the modified String in an UPDATE statement for the TempNarr table.
Users don't need to see the data refreshed in the form (although that
would be nice) as there's a report already setup to use the TempNarr
table as it's source. Below is the code I have so far, any help or even a
link would be fabulous!
TIA,
Susan


Private Sub FixNarr_Click()

DoCmd.SetWarnings True
DoCmd.RunSQL "Delete from TempNarr"
DoCmd.RunSQL "INSERT INTO TempNarr(MCODE, MCODETITLE," & _
"ESTHRS, PMCODE, NARR, MCAUSECODE) SELECT MCODE.MCODE," & _
"mcode.MCODETITLE, mcode.ESTHRS, mcode.PMCODE, mcode.NARR," & _
" mcode.MCAUSECODE FROM MCode" & _
" WHERE MCODE = Forms!frmFindMCode.MCode"

Dim strOld As String
Dim strFind As String
Dim strReplace As String
Dim strNew As String
Dim strOld1 As String
Dim strFind1 As String
Dim strReplace1 As String
Dim strNew1 As String

strFind = "\x0D"
strReplace = " " & Chr(13) & Chr(10)
strOld = Forms!frmFindMCode.NARR

MsgBox ("Narr: " & strOld)

strNew = Replace(strOld, strFind, strReplace)

strFind1 = "\x0A"
strReplace1 = ""
strOld1 = strNew
strNew1 = Replace(strOld1, strFind1, strReplace1)

MsgBox ("Now Narr: " & strNew1)

End Sub

 

[SusanV]

The SetWarnings False is an automatic typing thing when starting a new Sub
involving RunSQL, and when I'm still testing code I just change it to True
until I'm ready to go to production. Same with all the MsgBoxes - just
checkpoints so I can see what's happening to the variables when running the
code directly from the form via the button. They're all remarked out in
production, but I leave them there for troubleshooting later on in case
there's a problem.

Thanks for the tip on the Current.db way also - much appreciated! <copies
and pastes beneath other methods on cheatsheet>

;-)

You guys are the best!

Susan

Susan,

Well, if you're taking notes for future reference, here are a couple of
other comments...
1. I notice you have DoCmd.SetWarnings True in your code. Normally when
using DoCmdRunSQL or DoCmd.OpenQuery in code, it would be more usual to
turn the warnings off before the action queries, and on again afterwards.
2. An alternative to DoCmd.RunSQL, which seems to be more trendy at the
moment, and does not need any SetWarnings one way or the other, is like
this...
CurrentDb.Execute "UPDATE TempNarr SET NARR =
Replace('[NARR]','\x0A','')", dbFailOnError

--
Steve Schapel, Microsoft Access MVP

Excellent! Shorter than my current solution, and welcome indeed! I can't
believe I couldn't find this anywhere on the web or in the ngs. There's
more than one way to skin a cat!

<gratefully copies code to notepad for future reference>

Thanks!!!!

Susan

 

[Steve Schapel]

Susan,

The presence of ' or " characters in the data shouldn't have any impact
on the Replace() function. What difficulty are you experiencing?

 

[Steve Schapel]

Susan,

Mind you, I have just reviewed what I told you before, and see I made a
mistake. Oops. Sorry. There should not be ''s around the field name
in the replace expression. Should be like this...
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'\x0A','')"

 

[SusanV]

The error is:
Syntax error (missing operator) in query expression

<followed by the data in the field>

Every record that errors this way has either a " or a ' somewhere in the
field NARR. If I (in a backup copy of the data of course) edit the record to
remove the quotation mark or apostrophe the sub runs without error. There
are aver 5,000 records like this. Oh, and I unremarked the MsgBoxes, so I
can see it's definitely breaking on the UPDATE statement which is:
''''''''''''''''''''''''''''''
DoCmd.RunSQL "Update TempNarr Set NARR = """ & strFixed & _
""" where MCODE = Forms!frmFindMCode.MCode"
''''''''''''''''''''''''''''''

<sigh> I'm glad it's 5:00 I'll chew on this tomorrow. In the meantime,
thanks for your help!!!!

Susan

 

[SusanV]

Ah, so that's why that one didn't work...

Tomorrow is another day!

Thanks again for all your time and effort.

Susan

Susan,

Mind you, I have just reviewed what I told you before, and see I made a
mistake. Oops. Sorry. There should not be ''s around the field name in
the replace expression. Should be like this...
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'\x0A','')"

--
Steve Schapel, Microsoft Access MVP

Susan,

The presence of ' or " characters in the data shouldn't have any impact
on the Replace() function. What difficulty are you experiencing?

 

[Steve Schapel]

Susan,

Ah, ok, I thought you were referring to the code I gave you, not the one
using variables from your original over-complicated code. If you want
to use...
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'\x0A','')"
.... then "s and 's won't affect it.
If you want to use the complicated approach, yes you will need to cater
to the apostrophes. I think this will do it...
DoCmd.RunSQL "Update TempNarr Set NARR =" & """" & strFixed & """"
(Note I removed the WHERE clause, as it didn't seem to be doing anything
useful).

 

[John Nurick]

Hi Susan,

In recent versions of Access you can normally call the VBA Replace()
function in a query, e.g.

SELECT Replace([TheField], "String1", "String2") AS Processed ...

I'm not quite clear what's with the \x0D etc.

VBA doesn't do escaped sequences in strings, so "\x0D" is read as the
four characters \ x 0 D and not as a single CR. So to replace CRLFs with
spaces in a query you'd use

Replace([TheField], Chr(13) & Chr(10), " ")

"Recent versions" in this context means 2002 onwards - or 2000 with
recent Office and Jet service packs.

 

[SusanV]

Steve,

Thanks - that did the trick beautifully!
The code is now sleek and efficient, thanks to your help:
''''''''''''''''''''''
'Fix Narr
DoCmd.SetWarnings False
'Cleanup TempNarr table
DoCmd.RunSQL "Delete from TempNarr"
'Copy record to TempNarr table
DoCmd.RunSQL "INSERT INTO TempNarr(MCODE, MCODETITLE," & _
"ESTHRS, PMCODE, NARR, MCAUSECODE) SELECT MCODE.MCODE," & _
"mcode.MCODETITLE, mcode.ESTHRS, mcode.PMCODE, mcode.NARR," & _
" mcode.MCAUSECODE FROM MCode" & _
" WHERE MCODE = Forms!frmFindMCode.MCode"
'Remove \x0A and replace with space
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'\x0A','')"

'Remove \x0D and replace with CRLF
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'\x0D',' ' & Chr(13)
& Chr(10))"
'''''''''''''''''''''''''''

Again, many, many, MANY thanks - you saved me hours of agony!

Susan

Susan,

Ah, ok, I thought you were referring to the code I gave you, not the one
using variables from your original over-complicated code. If you want to
use...
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'\x0A','')"
... then "s and 's won't affect it.
If you want to use the complicated approach, yes you will need to cater to
the apostrophes. I think this will do it...
DoCmd.RunSQL "Update TempNarr Set NARR =" & """" & strFixed & """"
(Note I removed the WHERE clause, as it didn't seem to be doing anything
useful).

--
Steve Schapel, Microsoft Access MVP

The error is:
Syntax error (missing operator) in query expression

<followed by the data in the field>

Every record that errors this way has either a " or a ' somewhere in the
field NARR. If I (in a backup copy of the data of course) edit the record
to remove the quotation mark or apostrophe the sub runs without error.
There are aver 5,000 records like this. Oh, and I unremarked the
MsgBoxes, so I can see it's definitely breaking on the UPDATE statement
which is:
''''''''''''''''''''''''''''''
DoCmd.RunSQL "Update TempNarr Set NARR = """ & strFixed & _
""" where MCODE = Forms!frmFindMCode.MCode"
''''''''''''''''''''''''''''''

<sigh> I'm glad it's 5:00 I'll chew on this tomorrow. In the meantime,
thanks for your help!!!!

Susan

 

[Steve Schapel]

Susan,

I am happy to know that you have made progress here.

You should have a DoCmd.SetWarnings True at the end of your procedure

 

[SusanV]

Ah, good catch - thanks yet AGAIN!! I have added that, and some more
formatting to bring the data from upper to proper, everything is working
wonderfully. I just have to add more exceptions so common stuff like USNS
and MSC go back to upper. Easy beans for those, thanks to the replace
function, which I now actually understand! In case your curious or someone
might like to see upper to proper piece I've added, it's pasted below. When
i get more time I'll set this up to pull an array from an acronyms table
which the users can add to, saving a ton of hardcoding for the exceptions.

Again, thank you - there's no WAY I would have gotten the array so easily
without you giving me a true understanding of the DoCmd.RunSQL in
conjunction with the Replace function.

Susan

'''''''''''''''''''''''''''
'Change from upper to lower case
DoCmd.RunSQL "UPDATE TempNarr SET NARR = LCase([NARR])"
'Fix acronyms
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'msc', 'MSC')"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'navy', 'NAVY')"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'us', 'US')"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'mcode', 'MCode')"
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'note:', 'NOTE:')"

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

Susan,

I am happy to know that you have made progress here.

You should have a DoCmd.SetWarnings True at the end of your procedure

--
Steve Schapel, Microsoft Access MVP

Steve,

Thanks - that did the trick beautifully!
The code is now sleek and efficient, thanks to your help:
''''''''''''''''''''''
'Fix Narr
DoCmd.SetWarnings False
'Cleanup TempNarr table
DoCmd.RunSQL "Delete from TempNarr"
'Copy record to TempNarr table
DoCmd.RunSQL "INSERT INTO TempNarr(MCODE, MCODETITLE," & _
"ESTHRS, PMCODE, NARR, MCAUSECODE) SELECT MCODE.MCODE," & _
"mcode.MCODETITLE, mcode.ESTHRS, mcode.PMCODE, mcode.NARR," & _
" mcode.MCAUSECODE FROM MCode" & _
" WHERE MCODE = Forms!frmFindMCode.MCode"
'Remove \x0A and replace with space
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'\x0A','')"

'Remove \x0D and replace with CRLF
DoCmd.RunSQL "UPDATE TempNarr SET NARR = Replace([NARR],'\x0D',' ' &
Chr(13) & Chr(10))"
'''''''''''''''''''''''''''

Again, many, many, MANY thanks - you saved me hours of agony!

Susan