 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / April 2005Update field using String?
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 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. > 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 [quoted text clipped - 43 lines] > > End Sub 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 > DoCmd.RunSQL "Update TempNarr Set NARR = """ & strNew1 & _ > """ where MCODE = Forms!frmFindMCode.MCode" [quoted text clipped - 54 lines] >> >> End Sub 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  SignatureSteve 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 [quoted text clipped - 43 lines] > > End Sub 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, > [quoted text clipped - 60 lines] >> >> End Sub 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 SignatureSteve 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 [quoted text clipped - 5 lines] > > Susan 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, > [quoted text clipped - 18 lines] >> >> Susan 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, > [quoted text clipped - 60 lines] >> >> End Sub Susan, The presence of ' or " characters in the data shouldn't have any impact on the Replace() function. What difficulty are you experiencing? SignatureSteve Schapel, Microsoft Access MVP > 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, 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','')" SignatureSteve 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? Ah, so that's why that one didn't work... Tomorrow is another day! Thanks again for all your time and effort. Susan > Susan, > [quoted text clipped - 7 lines] >> The presence of ' or " characters in the data shouldn't have any impact >> on the Replace() function. What difficulty are you experiencing? 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 > Susan, > [quoted text clipped - 7 lines] >> >> 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). SignatureSteve Schapel, Microsoft Access MVP > The error is: > Syntax error (missing operator) in query expression [quoted text clipped - 15 lines] > > Susan 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, > [quoted text clipped - 29 lines] >> >> Susan 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 :-) SignatureSteve Schapel, Microsoft Access MVP > Steve, > [quoted text clipped - 22 lines] > > Susan 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, > [quoted text clipped - 28 lines] >> >> Susan 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. >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 [quoted text clipped - 43 lines] > >End Sub -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.