Built-in string functions not availble

M

Mark R. Rutter

Function SUBSTITUTE not available. When I run
Zip = Substitute("2003 Budget", "2003", "2004"), I get error message saying
that Function not defined. Why?
 
D

Douglas J. Steele

There's no function by that name built into any version of Access with which
I'm familiar.

Perhaps you mean the Replace function?
 
C

Chris O'C via AccessMonster.com

Access doesn't have any libraries that include the substitute function, so it
must be a user defined function in your app. If you've defined it somewhere,
it's either misspelled or it's out of scope of where you're calling it. If
the latter, it's probably either declared as private in a different module or
declared in a class module instead of a standard module.

If it's private make it public, if it's in a form or report or class module,
put it in a standard module.

Chris
Microsoft MVP
 
M

Mark R. Rutter

I found the answer.
SUBSTITUTE is a worksheet function with no equivalent VBA function. To use
the worksheet function in VBA, the code looks like,
Zip = WorksheetFunction.Substitute("2003 Budget", "2003", "2004").

The same applies to worksheet functions FIND and SEARCH
 
C

Chris O'C via AccessMonster.com

The WorksheetFunction class (and its functions) requires the Excel library,
something not included in the default Access libraries.

Chris
Microsoft MVP
 
Top