How to use Optional parameter?

[LAS]

I wanted to add a variation to a function that was in use in a lot of
places, so I thought if I added an Optional parameter, it wouldn't break
existing calls to the function. But if I use txtResult =
fncMinutesFormatted(232323) I get a "type mismatch" error. I have to put
something in the second parameter to avoid it. What am I doing wrong?

Public Function fncMinutesFormatted(ad_minutes As Double, Optional av_days
As Variant) As String

tia
las

 

[John W. Vinson]

I wanted to add a variation to a function that was in use in a lot of
places, so I thought if I added an Optional parameter, it wouldn't break
existing calls to the function. But if I use txtResult =
fncMinutesFormatted(232323) I get a "type mismatch" error. I have to put
something in the second parameter to avoid it. What am I doing wrong?

Public Function fncMinutesFormatted(ad_minutes As Double, Optional av_days
As Variant) As String

tia
las

Please post the actual SQL text of the query. If txtResult is a table field of
Text datatype, you need the syntactically required quotemarks (either ' or ")
surrounding the result of the function.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[LAS]

I guess my question should be "What do I do in the code of a function with
an optional parameter?) The missing av_days shows up as "missing" not as
"null", so I get an error on the line I indicate below.


Public Function fncMinutesFormatted(ad_minutes As Double, Optional av_days
As Variant) As String

On Error GoTo Err_Minutes

fncMinutesFormatted = "0"

If IsNull(ad_minutes) Then Exit Function
If Not IsNull(av_days) Then //WHEN AV_DAYS IS MISSING THIS LINE
CAUSES THE TYPE MISMATCH ERROR

But to try to answer your question for more information...

txtResult is a text box on a form. The call to the function is in the
clicked event of a button and looks like this:

txtResult = fncMinutesFormatted(44332, False) (this works fine - the
result shows up in txtResult)
txtResult = fncMinutesFormatted(44332) This returns the "type
mismatch" error.

Again, the definition of the function is:

Public Function fncMinutesFormatted(ad_minutes As Double, Optional av_days
As Variant) As String

 

[David W. Fenton]

I wanted to add a variation to a function that was in use in a lot
of places, so I thought if I added an Optional parameter, it
wouldn't break existing calls to the function. But if I use
txtResult = fncMinutesFormatted(232323) I get a "type mismatch"
error. I have to put something in the second parameter to avoid
it. What am I doing wrong?

Public Function fncMinutesFormatted(ad_minutes As Double, Optional
av_days As Variant) As String

What's the context in which you're using it? Code? A ControlSource
of a control? A query?

 

[LAS]

The code for the function is in a module. The call to the function is in a
form. Details of form in my earlier response.

 

[Stuart McCall]

I guess my question should be "What do I do in the code of a function with
an optional parameter?) The missing av_days shows up as "missing" not as
"null", so I get an error on the line I indicate below.


Public Function fncMinutesFormatted(ad_minutes As Double, Optional av_days
As Variant) As String

On Error GoTo Err_Minutes

fncMinutesFormatted = "0"

If IsNull(ad_minutes) Then Exit Function
If Not IsNull(av_days) Then //WHEN AV_DAYS IS MISSING THIS LINE
CAUSES THE TYPE MISMATCH ERROR

<snip>

You can use the IsMissing function to detect this condition, eg:

If Not IsMissing(av_days) Then
If Not IsNull(av_days) Then

 

[LAS]

Thanks to both you and Stuart. Learning lots from this newsgroup!

 

[David W. Fenton]

The code for the function is in a module. The call to the
function is in a form. Details of form in my earlier response.

Stuart has provided the answer (test for IsMissing() first, and then
for IsNull if it's not missing).

Generally, I don't use optional parameters of variant data type, so
have hardly ever encountered a need to use IsMissing(). Also, I
almost always provide a default value for optional parameters so I
don't have to test them at all (with a default value, it doesn't
matter if the parameter is missing -- the parameter gets
initialized, anyway).

One way to avoid two tests would be to supply a default value of
Null:

Public Function fncMinutesFormatted(ad_minutes As Double, _
Optional av_days As Variant = Null) As String

That way, av_days always has a value and you can ignore it when it's
Null and not have to test for both IsMissing() and IsNull().