The replace function (simple question)

[Not Me]

Hi,

I'm having bother with the replace function in access 2002, a while back I
remember not being able to get it to work... then suddenly the next time I
tried it did work. Now it doesn't again! I must be doing something wrong
but I don't really know how to change my approach to getting it working.

For example, if I type this in the vba immediate window:

?replace("HELLO","ELLO","ello")

I am returned with:
HELLO

so what's going on here?

Thanks!
Chris

[Wayne Morgan]

The syntax for the Replace function is

Replace(expression, find, replace[, start[, count[, compare]]])

The compare option at the end lets you specify the type of compare to do. If
this is run in a module with the compare option set in the declarations,
that should be used as the default if you don't specify here. To make sure
it always works the same, specify the compare options here.

?Replace("HELLO","ELLO","ello",,vbTextCompare)
Hello

?Replace("HELLO", "ELLO", "ello",,vbBinaryCompare)
HELLO

--
Wayne Morgan
MS Access MVP
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

Hi,

I'm having bother with the replace function in access 2002, a while back I
remember not being able to get it to work... then suddenly the next time I
tried it did work. Now it doesn't again! I must be doing something wrong
but I don't really know how to change my approach to getting it working.

For example, if I type this in the vba immediate window:

?replace("HELLO","ELLO","ello")

I am returned with:
HELLO

so what's going on here?

Thanks!
Chris

[Not Me]

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:Yq****************@newssvr22.news.prodigy.com ...

"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

For example, if I type this in the vba immediate window:

?replace("HELLO","ELLO","ello")

I am returned with:
HELLO

so what's going on here?

The syntax for the Replace function is
Replace(expression, find, replace[, start[, count[, compare]]]) <snip a little> ?Replace("HELLO","ELLO","ello",,vbTextCompare)
Hello

?Replace("HELLO", "ELLO", "ello",,vbBinaryCompare)
HELLO

Thanks for your reply,

When trying what you did, I get an error "compile error: wrong number of
arguments or invalid property assignment"

If I type 'replace(' into the code window the popup I get only shows 3
arguments?

Any clues?
Cheers,
Chris

[Wayne Morgan]

I tried it in Access 2003, they may have added an argument. Check your help
file for Replace and see what it offers (in the immediate window, place the
cursor in the word Replace and press F1). The thing that really has me
confused is that it shouldn't have made a difference in this case. I just
tried it again and it did NOT make a difference. I'll have to do some more
checking.

--
Wayne Morgan
MS Access MVP
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:Yq****************@newssvr22.news.prodigy.com ...

"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

For example, if I type this in the vba immediate window:

?replace("HELLO","ELLO","ello")

I am returned with:
HELLO

so what's going on here?

The syntax for the Replace function is
Replace(expression, find, replace[, start[, count[, compare]]])

<snip a little>

?Replace("HELLO","ELLO","ello",,vbTextCompare)
Hello

?Replace("HELLO", "ELLO", "ello",,vbBinaryCompare)
HELLO

Thanks for your reply,

When trying what you did, I get an error "compile error: wrong number of
arguments or invalid property assignment"

If I type 'replace(' into the code window the popup I get only shows 3
arguments?

Any clues?
Cheers,
Chris

[Wayne Morgan]

No, it didn't just work. I had a typo. However, I'm still checking on why
this is making a difference at all since the input string and compare string
are both upper case.

--
Wayne Morgan
MS Access MVP

[Wayne Morgan]

Ok, the typo was in the original response. I left out a comma.

?Replace("HELLO", "ELLO", "ello",,,vbBinaryCompare)
Hello

?Replace("HELLO", "ELLO", "ello",,,vbTextCompare)
Hello

?Replace("HELLO", "ello", "ello",,,vbTextCompare)
Hello

?Replace("HELLO", "ello", "ello",,,vbBinaryCompare)
HELLO

The above is what I would expect to happen and is what I'm getting now that
I have all of my commas in.

--
Wayne Morgan
MS Access MVP
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:Yq****************@newssvr22.news.prodigy.com ...

"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

For example, if I type this in the vba immediate window:

?replace("HELLO","ELLO","ello")

I am returned with:
HELLO

so what's going on here?

The syntax for the Replace function is
Replace(expression, find, replace[, start[, count[, compare]]])

<snip a little>

?Replace("HELLO","ELLO","ello",,vbTextCompare)
Hello

?Replace("HELLO", "ELLO", "ello",,vbBinaryCompare)
HELLO

Thanks for your reply,

When trying what you did, I get an error "compile error: wrong number of
arguments or invalid property assignment"

If I type 'replace(' into the code window the popup I get only shows 3
arguments?

Any clues?
Cheers,
Chris

[Not Me]

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:jB*****************@newssvr23.news.prodigy.co m...

Ok, the typo was in the original response. I left out a comma.

?Replace("HELLO", "ELLO", "ello",,,vbBinaryCompare)
Hello

?Replace("HELLO", "ELLO", "ello",,,vbTextCompare)
Hello

?Replace("HELLO", "ello", "ello",,,vbTextCompare)
Hello

?Replace("HELLO", "ello", "ello",,,vbBinaryCompare)
HELLO

The above is what I would expect to happen and is what I'm getting now that I have all of my commas in.

Thanks a lot for your troubles, when I look up the help it does indeed say
exactly what you're saying, however I keep getting the error message:
"compile error: wrong number of arguments or invalid property assignment".

The only way I avoid the error is using only 3 arguments, but then as before
it doesn't work!

Cheers,
Chris

[Wayne Morgan]

Can you copy and paste what you have into a message?

--
Wayne Morgan
MS Access MVP
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:jB*****************@newssvr23.news.prodigy.co m...

Ok, the typo was in the original response. I left out a comma.

?Replace("HELLO", "ELLO", "ello",,,vbBinaryCompare)
Hello

?Replace("HELLO", "ELLO", "ello",,,vbTextCompare)
Hello

?Replace("HELLO", "ello", "ello",,,vbTextCompare)
Hello

?Replace("HELLO", "ello", "ello",,,vbBinaryCompare)
HELLO

The above is what I would expect to happen and is what I'm getting now that

I have all of my commas in.

Thanks a lot for your troubles, when I look up the help it does indeed say
exactly what you're saying, however I keep getting the error message:
"compile error: wrong number of arguments or invalid property assignment".

The only way I avoid the error is using only 3 arguments, but then as

before it doesn't work!

Cheers,
Chris

[Terry Kreft]

It sounds to me as if you have a user defined function call ed Replace
somewhere.

Open a code module
Press the F2 key
In the search box at the top type Replace and search for it
Look to see if the Replace function is in your application or is the VBA one

Terry

"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:jB*****************@newssvr23.news.prodigy.co m...

Ok, the typo was in the original response. I left out a comma.

?Replace("HELLO", "ELLO", "ello",,,vbBinaryCompare)
Hello

?Replace("HELLO", "ELLO", "ello",,,vbTextCompare)
Hello

?Replace("HELLO", "ello", "ello",,,vbTextCompare)
Hello

?Replace("HELLO", "ello", "ello",,,vbBinaryCompare)
HELLO

The above is what I would expect to happen and is what I'm getting now that

I have all of my commas in.

Thanks a lot for your troubles, when I look up the help it does indeed say
exactly what you're saying, however I keep getting the error message:
"compile error: wrong number of arguments or invalid property assignment".

The only way I avoid the error is using only 3 arguments, but then as

before it doesn't work!

Cheers,
Chris

[Not Me]

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:BV********************@karoo.co.uk...

It sounds to me as if you have a user defined function call ed Replace
somewhere.

Open a code module
Press the F2 key
In the search box at the top type Replace and search for it
Look to see if the Replace function is in your application or is the VBA

one

D'oh!

Found one, hiding away in the corner of the database.. god knows where it
came from :o)

Cheers muchly,
Chris