Replace function - Is this normal?

[BruceS]

Hi, all. If this ends up appearing twice, I apologize. I got a "too busy"
error on the first attempt.

I'm running Access 2003 SP 3.

For the first time I can remember, I am trying to use the Replace function
with the "Start" parameter. Even when including all of the parameters
(Thanks, Doug!) it acts like it has the Mid fuction built in. Here is an
example copied out of my Immediate window:

mystr = "abc..def..ghijk"
? replace(mystr,"..","....",8,1,vbTextCompare)
f....ghijk

I was assuming that the output would be "abc..def....ghijk".

VBA help for Replace does not mention this "feature". Here is what it says
about "Start": "Optional. Position within expression where substring search
is to begin. If omitted, 1 is assumed."

I've tried it with "Count" set to both 1 and -1. Same results.

Is this behavior normal, or have the bit gremlins struck?

Thanks,
Bruce

 

[Graham Mandeno]

Hi Bruce

Well, I have to say I'm astonished, but that is actually the intended and
documented behaviour!

If you scroll to the bottom of the VBA help, it says:

======================
Remarks

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

It clearly states that the result begins at the Start position. Whoever
decided that was a good idea needs a good shaking!

 

[Linq Adams via AccessMonster.com]

So you'd need

Left(mystr, 7) & Replace(mystr,"..","....",8,1,vbTextCompare)

to get what you're aiming for.

Ain't Access just grand? ;0)>

 

[BruceS]

Thanks, my kiwi friend! It's nice to know that I'm not the only one thinking
it's wrong.

Hi Bruce

Well, I have to say I'm astonished, but that is actually the intended and
documented behaviour!

If you scroll to the bottom of the VBA help, it says:

======================
Remarks

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

It clearly states that the result begins at the Start position. Whoever
decided that was a good idea needs a good shaking!
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, all. If this ends up appearing twice, I apologize. I got a "too
busy"
error on the first attempt.

I'm running Access 2003 SP 3.

For the first time I can remember, I am trying to use the Replace function
with the "Start" parameter. Even when including all of the parameters
(Thanks, Doug!) it acts like it has the Mid fuction built in. Here is an
example copied out of my Immediate window:

mystr = "abc..def..ghijk"
? replace(mystr,"..","....",8,1,vbTextCompare)
f....ghijk

I was assuming that the output would be "abc..def....ghijk".

VBA help for Replace does not mention this "feature". Here is what it
says
about "Start": "Optional. Position within expression where substring
search
is to begin. If omitted, 1 is assumed."

I've tried it with "Count" set to both 1 and -1. Same results.

Is this behavior normal, or have the bit gremlins struck?

Thanks,
Bruce

 

[BruceS]

Thanks, Linq. It's what I ended up doing, after bitching a little more about
Replace(), of course.
Best,
Bruce