Replace function in Access 2000

  • Thread starter Thread starter Tommy Dahlqvist
  • Start date Start date
T

Tommy Dahlqvist

Hi,
I want to use the Replace-function, but can't get it to
work. Error: Undefined function "REPLACE"...
I use Win2000 (SP3), Access 2000 (SR-3).

What library/versions/files are necesary for get this to
work? I rather not upgrade to Access2002/2003 if it's
possible to avoid.

/Tommy
 
It should work --- unless you are trying to use it in a query. The Jet
Expression service does not recognize the VBA function replace in Access 2000.

Quote from Dirk Goldgar, MS Access MVP

The Replace function works in Access 2000 VBA, but it can't be used directly
in queries. You need to create a user-defined "wrapper" function that
receives arguments of the appropriate types, passes them on to the Replace
function, and returns the Replace function's result. Here's one:

'---- start of code ----
Function fncReplace(strExpression As String, _
strFind As String, _
strReplace As String, _
Optional lngStart As Long = 1, _
Optional lngCount As Long = -1, _
Optional lngCompare As Long = vbBinaryCompare) _
As String

fncReplace = Replace(strExpression, strFind, strReplace, _
lngStart, lngCount, lngCompare)

End Function
'---- end of code ----

I don't know offhand whether this problem has been fixed in Access 2002.
 
I've used VBA functions like Trim,Len,Left in ACCESS 2002 Queries and
it worked fine on development machine as well as testing machines with
Access Runtime only.
But recently on machines with Access Runtime I've started to see the
error
"'3075':Function is not available in expressions in query expression"

The only essential resent change(as far as I know) is that I installed
the latest Service Pack (Access2002 SP3) on my development machine.

Can this be the reason? If yes, should Microsoft investigate this?

The suggested workaround with wrapper functions is not nice, but works
fine, eg call in Query my own VBALeft,VBALen or VBATrim.

Public Function VBALeft(str As Variant, length As Variant)
VBALeft = VBA.Left(str, length)
End Function
Public Function VBALen(str As Variant)
VBALen = VBA.Len(str)
End Function
Public Function VBATrim(str As Variant)
VBATrim = VBA.Trim(str)
End Function

Michael Freidgeim
 
In this case, it is more likely a references issue. Your wrapper function is
specifying the library to use, so that is taking care of the problem.

To do its job, Access (like most modern programs) makes use of various
external program and object libraries that provide functionality that may be
shared among applications. For example, Access always uses the Visual Basic
for Applications library, the version-appropriate Access Object Library, and
the OLE Automation library. References to the specific library files,
including their locations, are stored with your database. But these library
modules may not be in the same location on different machines, especially if
they have different versions of Microsoft Office. If you move a database
from one machine to another, these references may be "broken"; that is, one
or more of the library files may not be where the stored reference says it
is.

When this happens, you need to take steps to let Access repair the broken
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are Doug Steele's instructions for how to do it:

****Quote****
Open any code module (or open the Immediate Window, using Ctrl-G, provided
you haven't selected the "keep debug window on top" option). Select Tools |
References from the menu bar. Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in).

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.
****Unquote****

So, follow those instructions and see if your problem goes away.

Check out the following reference
http://www.trigeminal.com/usenet/usenet026.asp?1036
 
Back
Top