replace function in expression in Access 2000

[wminter]

Hello

I am using a replace function in an expression in a query in Access 2002 that works fine:

replace(fax,'-','')

According to what I can gather, this
function should also work in Access 2000.
But when I try in on a client's machine
running Access 2000, it says the function
is not defined.

Any suggestions? Apart from upgrading the
client to Access 2002? Or changing the
numbers manually in the original table?

It would also be particularly helpful if
anyone could refer me to anyplace (printed
or on-line) which has a comparative list of standard string functions for Access 2000
and Access 2002.

Thanks

Bill Minter

 

[Steve101]

Bill,

Access2000 does'nt have a Replace function. One way to overcome this is to write your own, or better still, to 'borrow' the Excel replace capability. Here's how to do this:

(a) Create a new public module, and add the following code:

Function Replace(s1, s2, s3)
Replace = Excel.Application.WorksheetFunction.substitute(s1, s2, s3)
End Function

(b) Whilst still in the module, choose the Tools, References option, and tick on the "Microsoft Excel 9.0 Object Library" to make its procedures available to the program.

Save the module. Thats all there is to it. Your program should now be quite happy to use this replace function.

Hope this helps,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au