Does Replace$() function exist in Microsoft Access?

Question

It works, but is not listed here:
https://msdn.microsoft.com/en-us/library/office/gg278535.aspx
... > Office shared > Office VBA language reference > Visual Basic conceptual topics
Returning Strings from Functions

and in the VBA editor:
(View) - Object Browser - VBA - Strings

This function is discussed in many forums, so people use it.

Answer 1

The answer to your question depends on what you mean by "... exist[s] in Access". If we open an Immediate window in Access' VBA development environment and run

?Replace$("I like tofu!", "tofu", "bacon")

we get

I like bacon!

so clearly the Replace$ function does "exist" in Access itself. However, if we try to use Replace$ in a query against an Access database from an external application (e.g., VBScript, .NET, ...) we'll get

Undefined function 'Replace$' in expression.

Adding to the confusion is the fact that the older "Jet" ODBC/OLEDB drivers did not support the Replace function (without the dollar sign) but the newer "ACE" ODBC/OLEDB drivers do. (Neither flavour supports Replace$.) So a query like

SELECT Replace([Name], 'Gordon ', 'Gord ') AS newName FROM ...

will work if we use the ODBC driver

Driver={Microsoft Access Driver (*.mdb, .accdb)}

but not if we use

Driver={Microsoft Access Driver (*.mdb)}

Answer 2

The functions in the VBA.Strings module are "special" in how they are handled internally by VBA. For most of them, there are actually 2 versions in the type library - a version that returns a String (ends with $), and a version that returns a Variant. Internally these are declared as a pair of functions - for example, Right (from the vbe7.dll TypeLib):

[entry(618), helpcontext(0x000f6ea5)]
BSTR _stdcall _B_str_Right(
                [in] BSTR String, 
                [in] long Length);

[entry(619), helpcontext(0x000f656e)]
VARIANT _stdcall _B_var_Right(
                [in] VARIANT* String, 
                [in] long Length);

The compiler apparently treats the $ similarly to a "type hint" internally, because (using the example above), there actually isn't a function Left$ defined in the TypeLib. In fact, there isn't a function declared as VBA.Strings.Right either. These live in a special restricted interface named _HiddenInterface:

[
  odl,
  uuid(1E196B20-1F3C-1069-996B-00DD010EF676)
]
interface _HiddenInterface {
    ...
    [restricted, helpcontext(0x000f6d7c)]
    void _stdcall Right();
    ...
};

Note that Right$ doesn't appear in the in the _HiddenInterface, nor do any of the other string returning functions. The VBA compiler uses the "function type hint" to forward the function call to either _B_str_Right or _B_var_Right.

By now you're probably wonder what this has to do with your question. The answer is that Replace actually doesn't have two different internal representations. It always returns a string, doesn't exist on the _HiddenInterface, and lives directly in the VBA.Strings module:

[entry(712), helpstring("Find and replace a substring within a string"), helpcontext(0x000f6522)]
BSTR _stdcall Replace(
                [in] BSTR Expression, 
                [in] BSTR Find, 
                [in] BSTR Replace, 
                [in, optional, defaultvalue(1)] long Start, 
                [in, optional, defaultvalue(-1)] long Count, 
                [in, optional, defaultvalue(0),
  custom(270D72B0-FFB8-11CF-A4BD-00A0C90F26EE, 1)

] VbCompareMethod Compare);

Basically, there is no Replace$ function at all. VBA is treating the $ as a type hint for the return value (which is always a String anyway). As far as the ODBC and OLE drivers are concerned, I would imagine (TBH I really haven't looked into it much) that they are restricted to the names that are exposed by the TypeLib and are not interpreted by the VBA runtime as forwards to different functions. They simply don't exist if you're doing IDispatch lookups on vbe7.dll.