Optional Variables in Functions (REPLACE and other 'Worksheet' functions)

[Evi]

Although the article below claims that Access 2000 boasts the REPLACE
function, it isn't recognized when I try to use it in queries (pity this
wasn't made clear either in the article or in Access 2000 help). However, if
I use it in a module, as per the example , it is.
http://support.microsoft.com/kb/210465


2 Questions. Acc2K help, which doesn't seem to work properly anyway, is
peppered with things labelled as Worksheet Functions and the Help entry
seems to be suggesting that you are using them in Excel - I have not found
an indication to date that they are available for use in Access and whenever
I've tried to use them in queries, I get the Unrecognized Function error,
which led me to believe that whoever had written Acc2K help had an empty
bottle of vodka by his side at the time.
More on that here: http://eis.bris.ac.uk/~ccmjs/access2000.htm
So are they actually available in modules? Or do I have some reference
missing which would allow me to use them in a query.

2. If they are available only in modules, it wouldn't be that hard to use
these things by writing my own functions, except for a couple of things.

Access's own functions allow 2 things, ignoring nulls without error messages
and Optional variables
If I do my own version of Replace

Public Function ReplaceF(ByVal MyString, MyFind, MyReplace, MyStart,
MyCount) As String

ReplaceF=Replace(MyString,MyFind,MyReplace,MyStart, MyCount)
End Function

How do I indicate that the last 2 variables, MyStart and MyCount are
optional, as they are in the real Replace function?

How can I ensure that date and number functions don't end up as Strings
because I haven't defined them as date or number, yet will allow me to use
them when there might be a null value, just like many of Access's own
functions?

Evi

 

[John Spencer]

If you need to return NULLs you have to return a VARIANT since Null is not a
string. You can use OPTIONAL to indicate that an argument does not need to be
passed in and you can assign a value to the argument if nothing is passed in.

So I think this would be your rewritten function.

Public Function ReplaceF(ByVal MyString, MyFind, _
MyReplace, OPTIONAL MyStart = 1, _
OPTIONAL MyCount = -1) As VARIANT

ReplaceF=Replace(MyString,MyFind,MyReplace,MyStart, MyCount)

End Function

Access 2000 VBA included the Replace function; however it didn't make it over
to the SQL interpreter. I think it will work if you have 2000 at least SP2
installed for Access 2000.

As far as Access 2000 help showing EXCEL functions, I am stumped. I thought
the last version that would show Excel functions in the help was Access 97.
You can use Excel functions in Access by including the Excel Library in the
VBA references. I cannot give you any further guidance on that point.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Evi]

Thank you, John, that OPTIONAL thing is exactly what I needed.

I don't know why Access Help is so strange. Half the stuff which appears in
detail in Acc97 is missing and has been replaced to these strange references
to Worksheet functions. The hyperlinks within the help pages all give me
error messages even though I custom installed Office to include all
features.

It is possible that I need Service Pack 2. I tend to chicken out of Service
Packs unless something isn't working because of the problems they can cause.

Thank you again.

Evi