Replace Function

[paulmitchell507]

I am using Access 2003 and I would like to replace a specific value in
a string in table

.  I am using the function as the criteria to
update the current entry.

Replace([code],"7","8")

I want to replace the value 7 with value 8 in field [code].  The
problem I have is that I only want to replace the value 7 at string
position 5 in the code field. i.e.
AAAA7AA

Some of the field values have the number 7 at string positions before
position 5, I specifically want to change the value at position 5 and
not before
AA7A7AA - The replace function updates 7 at position 3 and 5

I tried to restrict the update to start at position 4

Replace([code],"7","8", 4)
Replace ( string1, find, replacement, [start, [count, [compare]]] )

But this removes all text in position 1-4.  I just want to replace the
value at string position 5.
Can anyone offer any advise?

 

[Jeff Boyce]

Rather than use the Replace() function, what about using an Update query,
and the Mid(), and Left() functions. It might look something like
(untested):
TEST: Mid(

,5,1)

Selection Criterion: "7"
Update to:              "8"

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[paulmitchell507]

Rather than use the Replace() function, what about using an Update query,
and the Mid(), and Left() functions.  It might look something like
(untested):
    TEST: Mid(

,5,1)

Selection Criterion: "7"
Update to:              "8"

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.




[QUOTE]
I am using Access 2003 and I would like to replace a specific value in
a string in table [code].  I am using the function as the criteria to
update the current entry. [QUOTE]
Replace([code],"7","8")[/QUOTE]

I want to replace the value 7 with value 8 in field [code].  The
problem I have is that I only want to replace the value 7 at string
position 5 in the code field. i.e.
AAAA7AA[/QUOTE]
[QUOTE]
Some of the field values have the number 7 at string positions before
position 5, I specifically want to change the value at position 5 and
not before
AA7A7AA - The replace function updates 7 at position 3 and 5[/QUOTE]
[QUOTE]
I tried to restrict the update to start at position 4[/QUOTE]
[QUOTE]
Replace([code],"7","8", 4)
Replace ( string1, find, replacement, [start, [count, [compare]]] )[/QUOTE]
[QUOTE]
But this removes all text in position 1-4.  I just want to replace the
value at string position 5.
Can anyone offer any advise?- Hide quoted text -[/QUOTE]

- Show quoted text -[/QUOTE]

Thank you very much, that's perfect.