IWETHEY v. 0.3.0 | TODO
1,095 registered users | 1 active user | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Replace function in MSAccess
I can't seem to find the Replace(strg, substringA, substringB) function which can be used in an MSAccess query.
New ObVB Mid$
-drl
New ...with ObVB InStr()
New Problem with that function
Is that it's not recursive. I could replace the first instance of the substring using that technique, but I may have the same substring multiple times within the same substring.

Solution is probably just to write a AccessVB function that uses the Mid$/InStr$ technique, but I am trying to avoid writing code.
New Re: Problem with that function
Just make a While loop until Instr is zero. This will be beastly slow if replacement is happening thousands of times. It's a really short loop though.

Or System("\\Cygwin\\bin\\sed...") nevermind.
-drl
New That can be dangerous for some cases
e.g. replaceWithLoop(replaceIn="abcc", replace="abc", with="ab")
--

"It\ufffds possible to build a reasonably prosperous society that invests in its people, doesn\ufffdt invade its neighbors, opposes Israel and stands up to America. (Just look at France.)"

-- James Lileks
New Why write when you can Copy-n-Paste? ;)
From [link|http://www.tek-tips.com/gfaqs.cfm/pid/705/fid/4342|http://www.tek-tips..../pid/705/fid/4342]

\n'------------------------------------------------------------------------------------\nPublic Function Replace(Expression As String, _\n                        Find As String, ByVal ReplaceWith As String, _\n                        Optional Start As Long = 1, Optional Count As Long = -1, _\n                        Optional Compare As Integer = vbBinaryCompare) As String\n'------------------------------------------------------------------------------------\n' Purpose: Replaces occurrences of a substring within a string with a specified\n'          repacement string.\n' Accepts: 1. String containing substrings to be replaced\n'          2. Value of the substring to be found\n'          3. Replacement value to substitute for the substring\n'          4. Optional starting position for the search, default=1 (start of string)\n'          5. Optional maximum number of occurrences to replace, default=-1 (all\n'             occurences)\n'          6. Optional text comparison method (default: binary compare)\n' Returns: The part of the string starting at the Start position, with substitutions\n'          made. Returns an empty string if Start > Len(Expression).\nDim str As String\nDim i As Long\nDim lngFindLen As Long\nDim c As Long\n\nIf Start < 1 Then Err.Raise 5\nIf Count < -1 Then Err.Raise 5\nIf Compare < 0 Or Compare >= 40 Then Err.Raise 5\nstr = Mid$(Expression, Start)\nlngFindLen = Len(Find)\nIf lngFindLen > 0 Then\nc = Count\ni = 1\nDo While c <> 0\ni = InStr(i, str, Find, Compare)\nIf i = 0 Then Exit Do\nIf lngFindLen = Len(ReplaceWith) Then\nMid$(str, i, lngFindLen) = ReplaceWith\nElse\nstr = Left$(str, i - 1) & ReplaceWith & Mid$(str, i + lngFindLen)\nEnd If\ni = i + Len(ReplaceWith)\nIf c > 0 Then c = c - 1\nLoop\nEnd If\nReplace = str\nEnd Function\n
--
Chris Altmann
Expand Edited by altmann Jan. 17, 2004, 01:46:47 AM EST
New That's not going to run very well...
...not without a line declaring the function and its arguments. ;)
New Which Version of Access?
Access 2002 (XP) seems to have it.
--
Chris Altmann
New Access 2000
I have the later version, I'll try it on that. Unfortunately, I'm trying to write a query that will be run by a cohort and he has and even older version.
New Access 2000 should have it
According to this: [link|http://support.microsoft.com/?kbid=210465|http://support.micro....com/?kbid=210465] , Access 2000 was the first to have that function. See my other post for a possible substitute for earlier versions.
--
Chris Altmann
     Replace function in MSAccess - (ChrisR) - (10)
         ObVB Mid$ -NT - (deSitter) - (6)
             ...with ObVB InStr() -NT - (FuManChu)
             Problem with that function - (ChrisR) - (4)
                 Re: Problem with that function - (deSitter) - (1)
                     That can be dangerous for some cases - (Arkadiy)
                 Why write when you can Copy-n-Paste? ;) - (altmann) - (1)
                     That's not going to run very well... - (FuManChu)
         Which Version of Access? - (altmann) - (2)
             Access 2000 - (ChrisR) - (1)
                 Access 2000 should have it - (altmann)

Please print clearly.
115 ms