The replace function (simple question)

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

re: The replace function (simple question)

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" <Not.Me@faker.fake.fa.ke> wrote in message
news:bqnivr$nuc$1@ucsnew1.ncl.ac.uk...[color=blue]
> 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
>
>[/color]

re: The replace function (simple question)

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:YqIzb.4289$ew1.398@newssvr22.news.prodigy.com ...[color=blue]
> "Not Me" <Not.Me@faker.fake.fa.ke> wrote in message
> news:bqnivr$nuc$1@ucsnew1.ncl.ac.uk...[color=green]
> > 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?[/color][/color]
[color=blue]
> The syntax for the Replace function is
> Replace(expression, find, replace[, start[, count[, compare]]])[/color]
<snip a little>[color=blue]
> ?Replace("HELLO","ELLO","ello",,vbTextCompare)
> Hello
>
> ?Replace("HELLO", "ELLO", "ello",,vbBinaryCompare)
> HELLO[/color]

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

re: The replace function (simple question)

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" <Not.Me@faker.fake.fa.ke> wrote in message
news:bqnm14$phu$1@ucsnew1.ncl.ac.uk...[color=blue]
> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
message[color=blue]
> news:YqIzb.4289$ew1.398@newssvr22.news.prodigy.com ...[color=green]
> > "Not Me" <Not.Me@faker.fake.fa.ke> wrote in message
> > news:bqnivr$nuc$1@ucsnew1.ncl.ac.uk...[color=darkred]
> > > 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?[/color][/color]
>[color=green]
> > The syntax for the Replace function is
> > Replace(expression, find, replace[, start[, count[, compare]]])[/color]
> <snip a little>[color=green]
> > ?Replace("HELLO","ELLO","ello",,vbTextCompare)
> > Hello
> >
> > ?Replace("HELLO", "ELLO", "ello",,vbBinaryCompare)
> > HELLO[/color]
>
> 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
>
>[/color]

re: The replace function (simple question)

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

re: The replace function (simple question)

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" <Not.Me@faker.fake.fa.ke> wrote in message
news:bqnm14$phu$1@ucsnew1.ncl.ac.uk...[color=blue]
> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
message[color=blue]
> news:YqIzb.4289$ew1.398@newssvr22.news.prodigy.com ...[color=green]
> > "Not Me" <Not.Me@faker.fake.fa.ke> wrote in message
> > news:bqnivr$nuc$1@ucsnew1.ncl.ac.uk...[color=darkred]
> > > 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?[/color][/color]
>[color=green]
> > The syntax for the Replace function is
> > Replace(expression, find, replace[, start[, count[, compare]]])[/color]
> <snip a little>[color=green]
> > ?Replace("HELLO","ELLO","ello",,vbTextCompare)
> > Hello
> >
> > ?Replace("HELLO", "ELLO", "ello",,vbBinaryCompare)
> > HELLO[/color]
>
> 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
>
>[/color]

re: The replace function (simple question)

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:jBOzb.3677$n%6.3595@newssvr23.news.prodigy.co m...[color=blue]
> 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[/color]
that[color=blue]
> I have all of my commas in.[/color]

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

re: The replace function (simple question)

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

--
Wayne Morgan
MS Access MVP


"Not Me" <Not.Me@faker.fake.fa.ke> wrote in message
news:bqpjac$jut$1@ucsnew1.ncl.ac.uk...[color=blue]
> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
message[color=blue]
> news:jBOzb.3677$n%6.3595@newssvr23.news.prodigy.co m...[color=green]
> > 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[/color]
> that[color=green]
> > I have all of my commas in.[/color]
>
> 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[/color]
before[color=blue]
> it doesn't work!
>
> Cheers,
> Chris
>
>[/color]

re: The replace function (simple question)

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" <Not.Me@faker.fake.fa.ke> wrote in message
news:bqpjac$jut$1@ucsnew1.ncl.ac.uk...[color=blue]
> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in[/color]
message[color=blue]
> news:jBOzb.3677$n%6.3595@newssvr23.news.prodigy.co m...[color=green]
> > 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[/color]
> that[color=green]
> > I have all of my commas in.[/color]
>
> 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[/color]
before[color=blue]
> it doesn't work!
>
> Cheers,
> Chris
>
>[/color]

re: The replace function (simple question)

"Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
news:BVydnRbY7sHjA02iXTWcmA@karoo.co.uk...[color=blue]
> 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[/color]
one

D'oh!

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

Cheers muchly,
Chris