By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Search and replace in a string

Scott Price
Expert 100+
P: 1,384
Hello again good folks!

MS Access 2003, WinXP SP2

This time I'm having a trouble with the logic required to search a text box for one of three particular strings ("degrees", "degree" and/or "deg") and replace it with Chr(176), which is the ANSI degree symbol... I've got a rather convoluted select case, nested if... then, nested do until ... loop structure which results in an endless loop/crashed program. (my test statement has been "Cook at 300 deg for 30 minutes, then 375 degrees for 20 minutes, put in 250 degree oven to keep warm until serving. Internal temp should never be below 140 degrees before serving." I want it to search for multiple instances of the three words, and replace each of them with the degree symbol...)

I'll paste in what I'm trying at the moment, hopefully someone can spot my mistake without too much trouble...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Instructions_AfterUpdate()
  2. Dim MyString As String, MyDeg As String, MyChr As Variant, MyChange As String, MyPos As Integer, MyPos2 As Integer
  3.  
  4. MyString = IIf(IsNull(Me.Instructions), 0, Me.Instructions)
  5. MyDeg = "degrees"
  6. MyChr = Chr$(176)
  7. MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  8. MyPos2 = Len(MyString)
  9. 'Debug.Print MyPos2
  10. 'Debug.Print MyPos
  11. Select Case MyPos
  12.     Case 0
  13.         MyDeg = "degree"
  14.         MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  15.         'Debug.Print MyPos
  16.         Select Case MyPos
  17.             Case 0
  18.                 MyDeg = "deg"
  19.                 MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  20.                 'Debug.Print MyPos
  21.                     If Not IsNull(MyPos) Or MyPos = 0 Then
  22.                         Do Until MyPos = 0
  23.                             MyChange = Left$(MyString, MyPos - 2) & Chr$(176) & Right$(MyString, MyPos2 - (MyPos + 4))
  24.                             Me.Instructions = MyChange
  25.                             MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  26.                         Loop
  27.                     End If
  28.             Case Is <> 0
  29.                 Do Until MyPos = 0
  30.                     MyChange = Left$(MyString, MyPos - 2) & Chr$(176) & Right$(MyString, MyPos2 - (MyPos + 5))
  31.                     Me.Instructions = MyChange
  32.                     MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  33.                 Loop
  34.         End Select
  35.     Case Is <> 0
  36.         Do Until MyPos = 0
  37.             MyChange = Left$(MyString, MyPos - 2) & Chr$(176) & Right$(MyString, MyPos2 - (MyPos + 6))
  38.             Me.Instructions = MyChange
  39.             MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  40.         Loop
  41. End Select
  42.  
  43. 'Debug.Print MyChange
  44.  
  45. End Sub
This gives me several errors and endless loop possibilities!

Anyone able to help me out?

Many thanks in advance.

Regards,
Scott
Aug 13 '07 #1
Share this Question
Share on Google+
16 Replies


JKing
Expert 100+
P: 1,206
Do you want the data to be stored with the degree symbol rather than one of the three words or are you just trying to change this for display purposes only?

If it's the first I suggest using an update query. Also access has a built in replace function for strings. Load up the help file on the replace function it should tell you everything you need to know.
Aug 13 '07 #2

Scott Price
Expert 100+
P: 1,384
Do you want the data to be stored with the degree symbol rather than one of the three words or are you just trying to change this for display purposes only?

If it's the first I suggest using an update query. Also access has a built in replace function for strings. Load up the help file on the replace function it should tell you everything you need to know.
We meet again, Jared... Thanks for looking at this one, too!

I'll have a look at the replace help file and will try the update query idea, and will be back to let you know if it will work for me or not.

Regards,
Scott
Aug 13 '07 #3

Scott Price
Expert 100+
P: 1,384
We meet again, Jared... Thanks for looking at this one, too!

I'll have a look at the replace help file and will try the update query idea, and will be back to let you know if it will work for me or not.

Regards,
Scott
Well, the Replace function appears to be exactly what I want, however it isn't working like I want yet! This is the code I have now:
Expand|Select|Wrap|Line Numbers
  1. MyDeg = " degrees"
  2. MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  3. 'Debug.Print MyPos
  4. If MyPos <> 0 Then
  5.     Me.Instructions = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
  6.     DoCmd.Save
  7. End If
  8.  
  9. MyDeg2 = " degree"
  10. MyPos2 = InStr(1, MyString, MyDeg2, vbTextCompare)
  11. 'Debug.Print MyPos2
  12. If MyPos2 <> 0 Then
  13.     Me.Instructions = Replace(MyString, MyDeg2, MyChr, , , vbTextCompare)
  14.     DoCmd.Save
  15. End If
  16.  
  17. MyDeg3 = " deg"
  18. MyPos3 = InStr(1, MyString, MyDeg3, vbTextCompare)
  19. 'Debug.Print MyPos3
  20. If MyPos3 <> 0 Then
  21.     Me.Instructions = Replace(MyString, MyDeg3, MyChr, , , vbTextCompare)
  22.     DoCmd.Save
  23. End If
  24.  
That takes my test statement " Cook at 300 deg for 30 minutes, then 375 degrees for 20 minutes, put in 250 degree oven to keep warm until serving. Internal temp should never be below 140 degrees before serving."

And returns this"Cook at 300° for 30 minutes, then 375°rees for 20 minutes, put in 250°ree oven to keep warm until serving. Internal temp should never be below 140°rees before serving."

Obviously it is firing the third MyDeg statement first, then when/if it gets around to firing the first, it finds nothing to replace. (I can prove this by commenting out the third MyDeg statement, it then fires the second one first!... commenting the second one then, leaving only the first, it will then and only then replace the ' degrees' with the deg symbol)

I have tried a 'hack-around' approach, by replacing my MyDeg and MyDeg2 arguments thusly: MyChr & "s", MyChr & "ree" in order to pick up the abandoned strings, but this only works if I manually modify then save the record three times...

Also have tried a For intCounter = 1 to 3 ... Next intCounter approach to force it to loop 3 times, but this doesn't work either! Still need to manually modify then save the record to replace all the unwantedness...

If I knew why it wasn't firing in the right order, or how to force it to fire in the right order in the first place, I wouldn't need to mess around with the For ... Next structure.

Any ideas?

Regards,
Scott
Aug 13 '07 #4

JKing
Expert 100+
P: 1,206
Before I go any further with this, are you attempting to change any occurence of degree, degrees, deg or degs for new instruction entries or are you trying to replace all the existing entries with those occurences?

If it's the first, I think you'll want to use the before update to alter the string before saving it into the database.

If it's the latter update queries are definitely the way to go. Even maybe for the first. Here's an example of what the update query might look like:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblRecipe SET tblRecipe.Instructions = Replace([tblrecipe].[Instructions],"degrees",Chr$(176));
  2.  
This would change all occurences of degrees to the symbol. Then just substitute degrees for degree and run the query again, then degs and finally deg.
Aug 13 '07 #5

Scott Price
Expert 100+
P: 1,384
Before I go any further with this, are you attempting to change any occurence of degree, degrees, deg or degs for new instruction entries or are you trying to replace all the existing entries with those occurences?
It started out to be both... However, not knowing about those handy-dandy Update queries I went through the db manually with the first function that I created (see my original post, though the posted version is a modified version of the one I used originally), and changed each instance already in the existing records.

So now I am mainly concerned about the new recipes that end users will enter, as well as changes to existing recipes that they may see fit to make.

Putting the code into the BeforeUpdate event is fine with me, however I still need it to search for, like you mentioned, every possible combination of "degrees", "degree", "degs", "deg" and replace each before saving the record.

Theoretically (as far as I can see, anyway) the latest code I posted SHOULD walk through the string replacing 'degrees' first, then 'degree', etc but it doesn't...

Thanks again, Jared,
Regards,
Scott
Aug 13 '07 #6

JKing
Expert 100+
P: 1,206
Well I figured out what's wrong. Basically, MyString never changes but you change the textbox 3 times.

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'In the first block MyString is equal to the original string from the textbox
  3. 'Textbox is changed with all occurences of  degrees replaced but no others
  4. MyDeg = " degrees"
  5. MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  6. 'Debug.Print MyPos
  7. If MyPos <> 0 Then
  8.     Me.Instructions = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
  9.     DoCmd.Save
  10. End If
  11.  
  12. 'Same thing MyString is still equal to the original string
  13. 'Textbox is changed with all occurences of degree replace but no others
  14. MyDeg2 = " degree"
  15. MyPos2 = InStr(1, MyString, MyDeg2, vbTextCompare)
  16. 'Debug.Print MyPos2
  17. If MyPos2 <> 0 Then
  18.     Me.Instructions = Replace(MyString, MyDeg2, MyChr, , , vbTextCompare)
  19.     DoCmd.Save
  20. End If
  21.  
  22. 'Same problem repeated once again
  23. 'However this is the last one and so the product of this block remains
  24. MyDeg3 = " deg"
  25. MyPos3 = InStr(1, MyString, MyDeg3, vbTextCompare)
  26. 'Debug.Print MyPos3
  27. If MyPos3 <> 0 Then
  28.     Me.Instructions = Replace(MyString, MyDeg3, MyChr, , , vbTextCompare)
  29.     DoCmd.Save
  30. End If
  31.  
Solution?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Dim MyString As String
  3.     Dim MyDeg As String
  4.     Dim MyChr As String
  5.     Dim MyChange As String
  6.     Dim MyPos As Integer
  7.  
  8.     MyChr = "*"
  9.     MyString = Me.Instruction
  10.     MyDeg = " degrees"
  11.     MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  12.     Debug.Print MyPos
  13.     If MyPos <> 0 Then
  14.         MyString = Replace(MyString, MyDeg, MyChr)
  15.     End If
  16.  
  17.     MyDeg = " degree"
  18.     MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  19.     Debug.Print MyPos
  20.     If MyPos <> 0 Then
  21.         MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
  22.     End If
  23.  
  24.     MyDeg = " deg"
  25.     MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  26.     Debug.Print MyPos
  27.     If MyPos <> 0 Then
  28.         MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
  29.     End If
  30.  
  31.     Me.Instruction = MyString
  32.  
  33. End Sub
  34.  
Aug 13 '07 #7

FishVal
Expert 2.5K+
P: 2,653
Well, the Replace function appears to be exactly what I want, however it isn't working like I want yet! This is the code I have now:
Expand|Select|Wrap|Line Numbers
  1. MyDeg = " degrees"
  2. MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  3. 'Debug.Print MyPos
  4. If MyPos <> 0 Then
  5.     Me.Instructions = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
  6.     DoCmd.Save
  7. End If
  8.  
  9. MyDeg2 = " degree"
  10. MyPos2 = InStr(1, MyString, MyDeg2, vbTextCompare)
  11. 'Debug.Print MyPos2
  12. If MyPos2 <> 0 Then
  13.     Me.Instructions = Replace(MyString, MyDeg2, MyChr, , , vbTextCompare)
  14.     DoCmd.Save
  15. End If
  16.  
  17. MyDeg3 = " deg"
  18. MyPos3 = InStr(1, MyString, MyDeg3, vbTextCompare)
  19. 'Debug.Print MyPos3
  20. If MyPos3 <> 0 Then
  21.     Me.Instructions = Replace(MyString, MyDeg3, MyChr, , , vbTextCompare)
  22.     DoCmd.Save
  23. End If
  24.  
That takes my test statement " Cook at 300 deg for 30 minutes, then 375 degrees for 20 minutes, put in 250 degree oven to keep warm until serving. Internal temp should never be below 140 degrees before serving."

And returns this"Cook at 300° for 30 minutes, then 375°rees for 20 minutes, put in 250°ree oven to keep warm until serving. Internal temp should never be below 140°rees before serving."

Obviously it is firing the third MyDeg statement first, then when/if it gets around to firing the first, it finds nothing to replace. (I can prove this by commenting out the third MyDeg statement, it then fires the second one first!... commenting the second one then, leaving only the first, it will then and only then replace the ' degrees' with the deg symbol)

I have tried a 'hack-around' approach, by replacing my MyDeg and MyDeg2 arguments thusly: MyChr & "s", MyChr & "ree" in order to pick up the abandoned strings, but this only works if I manually modify then save the record three times...

Also have tried a For intCounter = 1 to 3 ... Next intCounter approach to force it to loop 3 times, but this doesn't work either! Still need to manually modify then save the record to replace all the unwantedness...

If I knew why it wasn't firing in the right order, or how to force it to fire in the right order in the first place, I wouldn't need to mess around with the For ... Next structure.

Any ideas?

Regards,
Scott
Hi, Scott.

You say these 3 code blocks run one by one on MyString variable. Do you have instructions between them to update MyString with the results of previous replace???

BTW, I think a big part of the code is not needed. The following couple of code lines will do the job. ;)

Expand|Select|Wrap|Line Numbers
  1. MyString = Me.Instructions
  2. MyString=Replace(MyString, " degrees", MyChr, , , vbTextCompare)
  3. MyString=Replace(MyString, " degree", MyChr, , , vbTextCompare)
  4. MyString=Replace(MyString, " deg", MyChr, , , vbTextCompare)
  5. Me.Instructions = MyString
  6. DoCmd.Save
  7.  
Aug 13 '07 #8

Scott Price
Expert 100+
P: 1,384
Jared & FishVal, thanks guys... You're both geniuses!!

The final code that I am using (you're probably right, FishVal, that most of it is unneeded, but I haven't tested your method with a null string yet...)

In the BeforeUpdate event of the Instructions text box:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim MyString As String
  3. Dim MyDeg As String
  4. Dim MyChr As Variant
  5. Dim MyChange As String
  6. Dim MyPos As Integer
  7.  
  8. MyString = IIf(IsNull(Me.Instructions), 0, Me.Instructions)
  9. MyChr = Chr$(176)
  10. MyDeg = " degrees"
  11. MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  12. If MyPos <> 0 Then
  13.     MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
  14. End If
  15.  
  16. MyDeg = " degree"
  17. MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  18. If MyPos <> 0 Then
  19.     MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
  20. End If
  21.  
  22. MyDeg = " degs"
  23. MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  24. If MyPos <> 0 Then
  25.     MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
  26. End If
  27.  
  28. MyDeg = " deg"
  29. MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
  30. If MyPos <> 0 Then
  31.     MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
  32. End If
  33. Me.Instructions = MyString
  34. DoCmd.Save
Thanks again!

Regards,
Scott
Aug 13 '07 #9

JKing
Expert 100+
P: 1,206
I'd go with FishVals solution. It's shorter and more efficient. In the advent the user enters a null string the replace function return just that. If there are no occurences of any degree variants in the string it will remain the same.

Jared
Aug 13 '07 #10

Scott Price
Expert 100+
P: 1,384
Excuse me, this works fine in the AfterUpdate event... Using it in the BeforeUpdate event will give an error message.

As far as I'm concerned, though, AfterUpdate is fine.

Regards,
Scott
Aug 13 '07 #11

JKing
Expert 100+
P: 1,206
I tested mine in before update event and had no problem. The reason I suggested using the before update event as opposed to the after update event is because then you wouldn't be saving the record twice. Using the after update event you are saving the unreplaced version then editing it and saving it once again. In the before update you would be altering the string and then saving once.

Jared

Edit: For clarification I'm using the Before_Update Event of the form rather than the textbox.
Aug 13 '07 #12

Scott Price
Expert 100+
P: 1,384
I tested mine in before update event and had no problem. The reason I suggested using the before update event as opposed to the after update event is because then you wouldn't be saving the record twice. Using the after update event you are saving the unreplaced version then editing it and saving it once again. In the before update you would be altering the string and then saving once.

Jared
Hmm... The BeforeUpdate event gave me the error (off the top of my head, so the wording probably isn't correct on this error message): "The code you have placed in the before update event is preventing Access from saving this record." The debugger then highlights the line: Me.Instructions = MyString.

Copying and pasting the exact same code into the AfterUpdate event works fine without any errors.

I agree with you about the beforeupdate being more efficient, and only saving once, but if it gives me an error, it's useless to me!

Let me try a few things and get back to this later...

Regards,
Scott
Aug 13 '07 #13

JKing
Expert 100+
P: 1,206
I made a quick edit to my post above. I see the reason for your error and it's due to the fact that you're using the event of the textbox where as I was using the event of the form.

The issue of saving twice shouldn't be a problem using the afterupdate of the textbox. Sorry for the confusion.
Aug 13 '07 #14

Scott Price
Expert 100+
P: 1,384
Hmm... The BeforeUpdate event gave me the error (off the top of my head, so the wording probably isn't correct on this error message): "The code you have placed in the before update event is preventing Access from saving this record." The debugger then highlights the line: Me.Instructions = MyString.

Copying and pasting the exact same code into the AfterUpdate event works fine without any errors.

I agree with you about the beforeupdate being more efficient, and only saving once, but if it gives me an error, it's useless to me!

Let me try a few things and get back to this later...

Regards,
Scott
OK,

Using FishVal's solution in the BeforeUpdate event:

If a user begins to enter text, then decides against it, erases what they have entered and tries to leave the text box (thus saving the record according to the default save record behavior of access) It claims an invalid use of null, highlighting the line MyString = Me.Instructions...

Changing this line to MyString = IIF(IsNull(Me.Instructions),0,Me.Instructions)

Results in the same error as the other code on the BeforeUpdate event that I mentioned in my previous post: "The macro or function you have set in the BeforeUpdate or Validation Rule setting is preventing Access from saving this record." It then highlights the same line: Me.Instructions = MyString...

I checked the AllowZeroLength strings setting in the underlying table, and changed it to Yes, without any positive results.

Using this:
Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim MyString As String
  3.     Dim MyChr As String
  4.  
  5.     MyChr = Chr$(176)
  6.     MyString = IIf(IsNull(Me.Instructions), 0, Me.Instructions)
  7.     MyString = Replace(MyString, " degrees", MyChr, , , vbTextCompare)
  8.     MyString = Replace(MyString, " degree", MyChr, , , vbTextCompare)
  9.     MyString = Replace(MyString, " degs", MyChr, , , vbTextCompare)
  10.     MyString = Replace(MyString, " deg", MyChr, , , vbTextCompare)
  11.     Me.Instructions = MyString
  12.     DoCmd.Save
In the AfterUpdate event works perfectly, using exactly the same code in the BeforeUpdate event results in the error.

I'm not too worried about it at this point, so no need to respond to this, unless you really want to! As far as I'm concerned I'll just leave it as is in the AfterUpdate event and not worry about the error message...

Regards and thanks,
Scott
Aug 13 '07 #15

Scott Price
Expert 100+
P: 1,384
I made a quick edit to my post above. I see the reason for your error and it's due to the fact that you're using the event of the textbox where as I was using the event of the form.

The issue of saving twice shouldn't be a problem using the afterupdate of the textbox. Sorry for the confusion.

Sure appreciate that, Jared! This does erase the confusion... It now works using the code in the BeforeUpdate event of the FORM, instead of the text box!

Thanks again,
Scott
Aug 13 '07 #16

JKing
Expert 100+
P: 1,206
Scroll up to my last post and you'll see an explain for the confusion.

Glad to see you've got it all working though! Good luck with the rest of your project.

Jared
Aug 13 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.