A Replace function for MS Access 97

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a Replace function in MS Access 97? If not, how can I implement one?

For example, I would like to replace a specific character (such as "X") in a
string with another string (such as "XX").
 
I have one from Microsoft QuickBasic Programmer's Toolbox from 1988, but I
do not know whether it is proper to post it in the newsgroup. That was 16
years ago, but . . .

Mike G.
 
Here's one programmed after A2000 was released, so its functionality is
similar:

Function Replace(strExpr As String, strFind As String, strReplace As String,
Optional lngStart As Long = 1) As String
Dim strOut As String
Dim lngLenExpr As Long
Dim lngLenFind As Long
Dim lng As Long

lngLenExpr = Len(strExpr)
lngLenFind = Len(strFind)

If (lngLenExpr > 0) And (lngLenFind > 0) And (lngLenExpr >= lngStart)
Then
lng = lngStart
If lng > 1 Then
strOut = Left$(strExpr, lng - 1)
End If
Do While lng <= lngLenExpr
If Mid(strExpr, lng, lngLenFind) = strFind Then
strOut = strOut & strReplace
lng = lng + lngLenFind
Else
strOut = strOut & Mid(strExpr, lng, 1)
lng = lng + 1
End If
Loop
Replace = strOut
End If
End Function
 
When I run into this kind of stuff I link in the WSH library and let loose
with Regular Expressions. I haven't run into a string manipulation issue
that couldn't be most easily solved with RegEx. Of course the learning curve
on them is fairly steep and you do have to link them in but once you get
there, they're very very powerful.
Just my 2 cents.

Jon Furman
 
Hi Jon,

Would you be able to tell me where I could learn to use the WSH library for
Access 97? Also, is WSH require for normal Access users installation?
 
I forgot to mention, in Access 97 it's more difficult to get the WSH regex
library to properly link in. There's some kind of bug that prevents Access
from seeing it. Ordinarily what you do is just add a reference to the WSH
library in the references area of the VB macro editor. In newer versions of
Access this is all that is required, but in 97 it gets confused. If I
remember the correctly the workaround is to programmatically add the
reference but the details escape me at the moment. You may try a web search
or hopefully someone else can comment with more detail.

WSH isn't required with an Access install, but it is a pretty standard
component that exists in all newer versions of windows. I believe it's been
bundled with windows since Win98 or something like that.

As far learning it once you got the link working, anything good reference on
VBScript or Scripting in windows should say something about it.

Jon
 
I talk about how to add the reference programmatically in my October, 2003
article at http://members.rogers.com/douglas.j.steele/SmartAccess.html

However, it's probably safer _not_ to set a reference, and to use Late
Binding instead:

Dim reCurr As Object



Set reCurr = CreateObject("VBScript.RegExp")


Yes, you'll lose the advantage of Intellisense, but you'll not have
versioning problems if users have different versions installed.
 
That's even an even better way if you're stuck in 97. Thanks Doug.

I strongly urge learning regex for all kinds of string searches and
manipulations. They're convenient, they're useful and they also force us
programmers to think a little differently, which can't be a bad thing!


Jon
 
Here (with thanks to fellow Access MVP Joe Fallon) is my favourite Regular
Expressions resource ...

http://www.regular-expressions.info/

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top