Strange behavior of the Replace function

Question

This morning I discovered some strange behavior of the Replace function in A2003.

Instead of starting the search in a certain position, it DELETES all the characters until that position, See the next Sub as an example. The results of the different lines is placed behind the line as comment.

Sub Repl()
  Dim result As String
  'glo_stop
  
  result = Replace("appelflap", "flap", "moes", 1, 1)  'result = "appelmoes
  result = Replace("appelflap", "flap", "moes", 2, 1)  'result = "ppelmoes"
  result = Replace("appelflap", "flap", "moes", 6, 1)  'result = "moes"
  result = Replace("appelflap", "flap", "moes", 7, 1)  'result = "lap"
  
End Sub

Is my Access program corrupt, and needs a new installation?

Imb.

Answer 1

I tested it on a different computer, also with A2003 installed.

The results are the same, so it is not a corruption problem.

Or is it a misinterpretaion from my side how the function should work?

Imb.

Answer 2

This is not specific to Access; it uses the generic VBA function Replace.

The help for Replace explicitly states that the behavior that you describe is by design:

"The return value of the  Replace function is a string, with substitutions made, that begins at the position specified by start and and concludes at the end of the expression string. It is not a copy of the original string from start to finish."

(The "and and" is from the help subject...)

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 3

The help for Replace explicitly states that the behavior that you describe is by design:

Hi Hans,

Thank you. I think you are right, by design. Bad design in my opinion, however. MS had better called it Replace_and_mutilate. I can not (yet) imagine where to apply such a result.

In old days I used to have a function Replace_text, that functioned in the way I thought, so

    result = Replca_text("appelflapflap","flap","moes",10,1) with result = "appelflapmoes".

Perhaps I was biased a little bit in that way. On the other hand, the Dutch Help does not say clearly that the characters before the start-search-position are skipped: 

Notities

De resultaatwaarde van de functie Replace is een tekenreeks
waarin vervangingen zijn uitgevoerd. Deze vervangingen zijn gestart op de
positie, aangegeven in begin en uitgevoerd tot aan het einde van
de tekenreeks expressie. Het is geen kopie van de oorsponkelijke
tekenreeks vanaf het begin tot aan het einde.

Translate: These replacements are started on the position, defined in begin en executed until the end …

Also, the remark: It is not a copy … does not clear it: of course the result is not a copy of the original, because of the replacements.

What I tried to realize is this. On each form I have a button "Zoom". When pressed, a general Zoom_form is opened with the value of the current control. The value is displayed in a control with much more height than in the referring control, so you can better see line breaks, or longer texts.

In this Zoom_form, I have all kind of other functionality (depending on user level) to do analyses or string manipulations. You can see the ASCII values, change to upper case or lower case, change font or fontsize. etc.

For to show the ASCII values, I had already included that if a part of the string is selected, only the selected part is considered. This now I wanted to extend to the - partially - conversion to upper or lower case. And that did not work with just Replace.

In between I found a workable solution to preceed the result with the part that is skipped. But why that is not "standard" done in the Replace function.

Imb.