UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Replace Function, Access 2016    
 
   
jsurpless
post Jun 27 2019, 08:55 AM
Post#1



Posts: 487
Joined: 21-December 03



Hi

I have a string '001_P_1001' that I would like to replace the 2nd '001' with '002'

So I am trying to trying to use the Replace function like so

Replace('001_P_1001','001','002',2')

so that it doesn't find the the 1st '001' string

Unfortunately, what it is doing is that it is returning is '01_P_1002' instead of '001_P_1002'

I realize that I could search for '1002' as an alternative but I would prefer not to...

Is there anyway to not have the Replace function behave in this way?

Thank you!
Go to the top of the page
 
DanielPineault
post Jun 27 2019, 09:12 AM
Post#2


UtterAccess VIP
Posts: 6,978
Joined: 30-June 11



You could do something along the lines of

CODE
sInput = "001_P_1001"
?Left(sInput, 4) & Replace(sInput, "001", "002", 5, 1)

or
CODE
sInput = "001_P_1001"
?Left(sInput, 1) & Replace(sInput, "001", "002", 2, 1)

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
jsurpless
post Jun 27 2019, 09:15 AM
Post#3



Posts: 487
Joined: 21-December 03



OK, I thought that might be what I had to do, thanks for confirming that this is just how Replace works!
Go to the top of the page
 
DanielPineault
post Jun 27 2019, 09:25 AM
Post#4


UtterAccess VIP
Posts: 6,978
Joined: 30-June 11



Yes, it's not the behavior I'd like either, but he documentation is clear (as is the result you get)

QUOTE
start Optional. Start position for the substring of expression to be searched and returned.


If you search online you can also find user defined functions to replace the nth term, perhaps that would be more worth your while to find and implement. I have such a function that I've developed for a project years ago, but can't locate it right now (of course when I want it).

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
jsurpless
post Jun 27 2019, 09:28 AM
Post#5



Posts: 487
Joined: 21-December 03



Oh, that essential tidbit wasn't mentioned in the article I was referencing

https://www.techonthenet.com/access/functio...ing/replace.php

Thanks for pointing that out!
Go to the top of the page
 
DanielPineault
post Jun 27 2019, 10:03 AM
Post#6


UtterAccess VIP
Posts: 6,978
Joined: 30-June 11



Always look at the official Microsoft documentation

https://docs.microsoft.com/en-us/office/vba...eplace-function

and then search from there.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
kfield7
post Jun 27 2019, 11:03 AM
Post#7



Posts: 1,014
Joined: 12-November 03
From: Iowa Lot


I had the same issue here.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd November 2019 - 09:20 PM