Search and replace in a string

[Scott Price]

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

Private Sub Instructions_AfterUpdate()
Dim MyString As String, MyDeg As String, MyChr As Variant, MyChange As String, MyPos As Integer, MyPos2 As Integer
 
MyString = IIf(IsNull(Me.Instructions), 0, Me.Instructions)
MyDeg = "degrees"
MyChr = Chr$(176)
MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
MyPos2 = Len(MyString)
'Debug.Print MyPos2
'Debug.Print MyPos
Select Case MyPos
    Case 0
        MyDeg = "degree"
        MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
        'Debug.Print MyPos
        Select Case MyPos
            Case 0
                MyDeg = "deg"
                MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
                'Debug.Print MyPos
                    If Not IsNull(MyPos) Or MyPos = 0 Then
                        Do Until MyPos = 0
                            MyChange = Left$(MyString, MyPos - 2) & Chr$(176) & Right$(MyString, MyPos2 - (MyPos + 4))
                            Me.Instructions = MyChange
                            MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
                        Loop
                    End If
            Case Is <> 0
                Do Until MyPos = 0
                    MyChange = Left$(MyString, MyPos - 2) & Chr$(176) & Right$(MyString, MyPos2 - (MyPos + 5))
                    Me.Instructions = MyChange
                    MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
                Loop
        End Select
    Case Is <> 0
        Do Until MyPos = 0
            MyChange = Left$(MyString, MyPos - 2) & Chr$(176) & Right$(MyString, MyPos2 - (MyPos + 6))
            Me.Instructions = MyChange
            MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
        Loop
End Select
 
'Debug.Print MyChange
 
End Sub

This gives me several errors and endless loop possibilities!

Anyone able to help me out?

Many thanks in advance.

Regards,
Scott

[JKing]

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.

[Scott Price]

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

[Scott Price]

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:

MyDeg = " degrees"
MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
'Debug.Print MyPos
If MyPos <> 0 Then
    Me.Instructions = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
    DoCmd.Save
End If
 
MyDeg2 = " degree"
MyPos2 = InStr(1, MyString, MyDeg2, vbTextCompare)
'Debug.Print MyPos2
If MyPos2 <> 0 Then
    Me.Instructions = Replace(MyString, MyDeg2, MyChr, , , vbTextCompare)
    DoCmd.Save
End If
 
MyDeg3 = " deg"
MyPos3 = InStr(1, MyString, MyDeg3, vbTextCompare)
'Debug.Print MyPos3
If MyPos3 <> 0 Then
    Me.Instructions = Replace(MyString, MyDeg3, MyChr, , , vbTextCompare)
    DoCmd.Save
End If
 

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

[JKing]

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:

UPDATE tblRecipe SET tblRecipe.Instructions = Replace([tblrecipe].[Instructions],"degrees",Chr$(176));
 

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.

[Scott Price]

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

[JKing]

Well I figured out what's wrong. Basically, MyString never changes but you change the textbox 3 times.

 
'In the first block MyString is equal to the original string from the textbox
'Textbox is changed with all occurences of  degrees replaced but no others
MyDeg = " degrees"
MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
'Debug.Print MyPos
If MyPos <> 0 Then
    Me.Instructions = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
    DoCmd.Save
End If
 
'Same thing MyString is still equal to the original string
'Textbox is changed with all occurences of degree replace but no others
MyDeg2 = " degree"
MyPos2 = InStr(1, MyString, MyDeg2, vbTextCompare)
'Debug.Print MyPos2
If MyPos2 <> 0 Then
    Me.Instructions = Replace(MyString, MyDeg2, MyChr, , , vbTextCompare)
    DoCmd.Save
End If
 
'Same problem repeated once again
'However this is the last one and so the product of this block remains
MyDeg3 = " deg"
MyPos3 = InStr(1, MyString, MyDeg3, vbTextCompare)
'Debug.Print MyPos3
If MyPos3 <> 0 Then
    Me.Instructions = Replace(MyString, MyDeg3, MyChr, , , vbTextCompare)
    DoCmd.Save
End If
 

Solution?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim MyString As String
    Dim MyDeg As String
    Dim MyChr As String
    Dim MyChange As String
    Dim MyPos As Integer
 
    MyChr = "*"
    MyString = Me.Instruction
    MyDeg = " degrees"
    MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
    Debug.Print MyPos
    If MyPos <> 0 Then
        MyString = Replace(MyString, MyDeg, MyChr)
    End If
 
    MyDeg = " degree"
    MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
    Debug.Print MyPos
    If MyPos <> 0 Then
        MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
    End If
 
    MyDeg = " deg"
    MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
    Debug.Print MyPos
    If MyPos <> 0 Then
        MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
    End If
 
    Me.Instruction = MyString
 
End Sub
 

[FishVal]

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:

MyDeg = " degrees"
MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
'Debug.Print MyPos
If MyPos <> 0 Then
    Me.Instructions = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
    DoCmd.Save
End If
 
MyDeg2 = " degree"
MyPos2 = InStr(1, MyString, MyDeg2, vbTextCompare)
'Debug.Print MyPos2
If MyPos2 <> 0 Then
    Me.Instructions = Replace(MyString, MyDeg2, MyChr, , , vbTextCompare)
    DoCmd.Save
End If
 
MyDeg3 = " deg"
MyPos3 = InStr(1, MyString, MyDeg3, vbTextCompare)
'Debug.Print MyPos3
If MyPos3 <> 0 Then
    Me.Instructions = Replace(MyString, MyDeg3, MyChr, , , vbTextCompare)
    DoCmd.Save
End If
 

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

MyString = Me.Instructions
MyString=Replace(MyString, " degrees", MyChr, , , vbTextCompare)
MyString=Replace(MyString, " degree", MyChr, , , vbTextCompare)
MyString=Replace(MyString, " deg", MyChr, , , vbTextCompare)
Me.Instructions = MyString
DoCmd.Save
 

[Scott Price]

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:

 
Dim MyString As String
Dim MyDeg As String
Dim MyChr As Variant
Dim MyChange As String
Dim MyPos As Integer
 
MyString = IIf(IsNull(Me.Instructions), 0, Me.Instructions)
MyChr = Chr$(176)
MyDeg = " degrees"
MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
If MyPos <> 0 Then
    MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
End If
 
MyDeg = " degree"
MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
If MyPos <> 0 Then
    MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
End If
 
MyDeg = " degs"
MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
If MyPos <> 0 Then
    MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
End If
 
MyDeg = " deg"
MyPos = InStr(1, MyString, MyDeg, vbTextCompare)
If MyPos <> 0 Then
    MyString = Replace(MyString, MyDeg, MyChr, , , vbTextCompare)
End If
Me.Instructions = MyString
DoCmd.Save

Thanks again!

Regards,
Scott

[JKing]

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

[Scott Price]

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

[JKing]

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.

[Scott Price]

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

[JKing]

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.

[Scott Price]

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:

 
    Dim MyString As String
    Dim MyChr As String
 
    MyChr = Chr$(176)
    MyString = IIf(IsNull(Me.Instructions), 0, Me.Instructions)
    MyString = Replace(MyString, " degrees", MyChr, , , vbTextCompare)
    MyString = Replace(MyString, " degree", MyChr, , , vbTextCompare)
    MyString = Replace(MyString, " degs", MyChr, , , vbTextCompare)
    MyString = Replace(MyString, " deg", MyChr, , , vbTextCompare)
    Me.Instructions = MyString
    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

[Scott Price]

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

[JKing]

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