I have some optional arguments in a function:

Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
As Long) As Long

This is called with the following line:

mlngPatientID = InsertRecord("usp_NewPatient_Insert")

The second optional argument is not passed. In the function, I check for a
value in the 2nd argument with:

If Not IsNull(brSecondaryKeyValue) Then

This is executing as if there is a value in brSecondaryKeyValue. How do I
clear this value? Do I set the Object (it's not an object), or make it equal
to zero or null?

Thanks again!!
--
sam

What value do you get if you add this line immediately following the "Public
Function..."
MsgBox "brSecondaryKeyValue: " & brSecondaryKeyValue
I expect since you have declared it as Long, it will default to 0 unless you
provide a default in the function line. If you need to determine if a value
was passed in you could check for 0 or possibly set the default to an
unrealistic value like -999999 and then check for this value.

--
Duane Hookom
MS Access MVP


"smk23" <(E-Mail Removed)> wrote in message
news:EB324CA2-596E-4CAD-896A-(E-Mail Removed)...
>I have some optional arguments in a function:
>
> Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
> As Long) As Long
>
> This is called with the following line:
>
> mlngPatientID = InsertRecord("usp_NewPatient_Insert")
>
> The second optional argument is not passed. In the function, I check for
> a
> value in the 2nd argument with:
>
> If Not IsNull(brSecondaryKeyValue) Then
>
> This is executing as if there is a value in brSecondaryKeyValue. How do I
> clear this value? Do I set the Object (it's not an object), or make it
> equal
> to zero or null?
>
> Thanks again!!
> --
> sam

smk23 wrote:

>I have some optional arguments in a function:
>
>Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
>As Long) As Long
>
>This is called with the following line:
>
> mlngPatientID = InsertRecord("usp_NewPatient_Insert")
>
>The second optional argument is not passed. In the function, I check for a
>value in the 2nd argument with:
>
> If Not IsNull(brSecondaryKeyValue) Then
>
>This is executing as if there is a value in brSecondaryKeyValue. How do I
>clear this value? Do I set the Object (it's not an object), or make it equal
>to zero or null?


In addition to Duane's explanation of using the arguments
default value, if you can live with declaring the optional
argument as Variant, then you could use the IsMissing
funtion.

--
Marsh
MVP [MS Access]

Yes, the only really reliable way to use optional arguments is to declare
them as Variant. No other data type as a "missing" bit.

"Marshall Barton" wrote:

> smk23 wrote:
>
> >I have some optional arguments in a function:
> >
> >Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
> >As Long) As Long
> >
> >This is called with the following line:
> >
> > mlngPatientID = InsertRecord("usp_NewPatient_Insert")
> >
> >The second optional argument is not passed. In the function, I check for a
> >value in the 2nd argument with:
> >
> > If Not IsNull(brSecondaryKeyValue) Then
> >
> >This is executing as if there is a value in brSecondaryKeyValue. How do I
> >clear this value? Do I set the Object (it's not an object), or make it equal
> >to zero or null?
>
>
> In addition to Duane's explanation of using the arguments
> default value, if you can live with declaring the optional
> argument as Variant, then you could use the IsMissing
> funtion.
>
> --
> Marsh
> MVP [MS Access]
>

IME optional arguments always work reliably provided you remember that
when an optional argument is omitted from the call, it is still passed
to the called procedure but with a default value.

So if a procedure is declared as
Sub Foo(Optional X As Long)
and called as
Foo
it will receive the default value of a VBA Long, namely 0. For a
different default value, include it in the declaration:
Sub Foo(Optional X As Long = -1)

Variants work slightly differently. If you declare an optional argument
as Variant and don't explicitly set a default value, then and only then
you can use IsMissing() to detect whether or not the the calling code
passed a value.



On Mon, 20 Jun 2005 10:56:09 -0700, Klatuu
<(E-Mail Removed)> wrote:

>Yes, the only really reliable way to use optional arguments is to declare
>them as Variant. No other data type as a "missing" bit.
>
>"Marshall Barton" wrote:
>
>> smk23 wrote:
>>
>> >I have some optional arguments in a function:
>> >
>> >Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
>> >As Long) As Long
>> >
>> >This is called with the following line:
>> >
>> > mlngPatientID = InsertRecord("usp_NewPatient_Insert")
>> >
>> >The second optional argument is not passed. In the function, I check for a
>> >value in the 2nd argument with:
>> >
>> > If Not IsNull(brSecondaryKeyValue) Then
>> >
>> >This is executing as if there is a value in brSecondaryKeyValue. How do I
>> >clear this value? Do I set the Object (it's not an object), or make it equal
>> >to zero or null?
>>
>>
>> In addition to Duane's explanation of using the arguments
>> default value, if you can live with declaring the optional
>> argument as Variant, then you could use the IsMissing
>> funtion.
>>
>> --
>> Marsh
>> MVP [MS Access]
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

John,

You are correct. That is why passing optional arguments with Variant is
more reliable. If you have a long identified in your function as an
argument, you don't know whether the 0 was passed or it is the default value.

"John Nurick" wrote:

> IME optional arguments always work reliably provided you remember that
> when an optional argument is omitted from the call, it is still passed
> to the called procedure but with a default value.
>
> So if a procedure is declared as
> Sub Foo(Optional X As Long)
> and called as
> Foo
> it will receive the default value of a VBA Long, namely 0. For a
> different default value, include it in the declaration:
> Sub Foo(Optional X As Long = -1)
>
> Variants work slightly differently. If you declare an optional argument
> as Variant and don't explicitly set a default value, then and only then
> you can use IsMissing() to detect whether or not the the calling code
> passed a value.
>
>
>
> On Mon, 20 Jun 2005 10:56:09 -0700, Klatuu
> <(E-Mail Removed)> wrote:
>
> >Yes, the only really reliable way to use optional arguments is to declare
> >them as Variant. No other data type as a "missing" bit.
> >
> >"Marshall Barton" wrote:
> >
> >> smk23 wrote:
> >>
> >> >I have some optional arguments in a function:
> >> >
> >> >Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
> >> >As Long) As Long
> >> >
> >> >This is called with the following line:
> >> >
> >> > mlngPatientID = InsertRecord("usp_NewPatient_Insert")
> >> >
> >> >The second optional argument is not passed. In the function, I check for a
> >> >value in the 2nd argument with:
> >> >
> >> > If Not IsNull(brSecondaryKeyValue) Then
> >> >
> >> >This is executing as if there is a value in brSecondaryKeyValue. How do I
> >> >clear this value? Do I set the Object (it's not an object), or make it equal
> >> >to zero or null?
> >>
> >>
> >> In addition to Duane's explanation of using the arguments
> >> default value, if you can live with declaring the optional
> >> argument as Variant, then you could use the IsMissing
> >> funtion.
> >>
> >> --
> >> Marsh
> >> MVP [MS Access]
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

I think we must have different criteria for reliability.<g>


On Tue, 21 Jun 2005 06:34:02 -0700, Klatuu
<(E-Mail Removed)> wrote:

>John,
>
>You are correct. That is why passing optional arguments with Variant is
>more reliable. If you have a long identified in your function as an
>argument, you don't know whether the 0 was passed or it is the default value.
>
>"John Nurick" wrote:
>
>> IME optional arguments always work reliably provided you remember that
>> when an optional argument is omitted from the call, it is still passed
>> to the called procedure but with a default value.
>>
>> So if a procedure is declared as
>> Sub Foo(Optional X As Long)
>> and called as
>> Foo
>> it will receive the default value of a VBA Long, namely 0. For a
>> different default value, include it in the declaration:
>> Sub Foo(Optional X As Long = -1)
>>
>> Variants work slightly differently. If you declare an optional argument
>> as Variant and don't explicitly set a default value, then and only then
>> you can use IsMissing() to detect whether or not the the calling code
>> passed a value.
>>
>>
>>
>> On Mon, 20 Jun 2005 10:56:09 -0700, Klatuu
>> <(E-Mail Removed)> wrote:
>>
>> >Yes, the only really reliable way to use optional arguments is to declare
>> >them as Variant. No other data type as a "missing" bit.
>> >
>> >"Marshall Barton" wrote:
>> >
>> >> smk23 wrote:
>> >>
>> >> >I have some optional arguments in a function:
>> >> >
>> >> >Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
>> >> >As Long) As Long
>> >> >
>> >> >This is called with the following line:
>> >> >
>> >> > mlngPatientID = InsertRecord("usp_NewPatient_Insert")
>> >> >
>> >> >The second optional argument is not passed. In the function, I check for a
>> >> >value in the 2nd argument with:
>> >> >
>> >> > If Not IsNull(brSecondaryKeyValue) Then
>> >> >
>> >> >This is executing as if there is a value in brSecondaryKeyValue. How do I
>> >> >clear this value? Do I set the Object (it's not an object), or make it equal
>> >> >to zero or null?
>> >>
>> >>
>> >> In addition to Duane's explanation of using the arguments
>> >> default value, if you can live with declaring the optional
>> >> argument as Variant, then you could use the IsMissing
>> >> funtion.
>> >>
>> >> --
>> >> Marsh
>> >> MVP [MS Access]
>> >>
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

define reliable

"John Nurick" wrote:

> I think we must have different criteria for reliability.<g>
>
>
> On Tue, 21 Jun 2005 06:34:02 -0700, Klatuu
> <(E-Mail Removed)> wrote:
>
> >John,
> >
> >You are correct. That is why passing optional arguments with Variant is
> >more reliable. If you have a long identified in your function as an
> >argument, you don't know whether the 0 was passed or it is the default value.
> >
> >"John Nurick" wrote:
> >
> >> IME optional arguments always work reliably provided you remember that
> >> when an optional argument is omitted from the call, it is still passed
> >> to the called procedure but with a default value.
> >>
> >> So if a procedure is declared as
> >> Sub Foo(Optional X As Long)
> >> and called as
> >> Foo
> >> it will receive the default value of a VBA Long, namely 0. For a
> >> different default value, include it in the declaration:
> >> Sub Foo(Optional X As Long = -1)
> >>
> >> Variants work slightly differently. If you declare an optional argument
> >> as Variant and don't explicitly set a default value, then and only then
> >> you can use IsMissing() to detect whether or not the the calling code
> >> passed a value.
> >>
> >>
> >>
> >> On Mon, 20 Jun 2005 10:56:09 -0700, Klatuu
> >> <(E-Mail Removed)> wrote:
> >>
> >> >Yes, the only really reliable way to use optional arguments is to declare
> >> >them as Variant. No other data type as a "missing" bit.
> >> >
> >> >"Marshall Barton" wrote:
> >> >
> >> >> smk23 wrote:
> >> >>
> >> >> >I have some optional arguments in a function:
> >> >> >
> >> >> >Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
> >> >> >As Long) As Long
> >> >> >
> >> >> >This is called with the following line:
> >> >> >
> >> >> > mlngPatientID = InsertRecord("usp_NewPatient_Insert")
> >> >> >
> >> >> >The second optional argument is not passed. In the function, I check for a
> >> >> >value in the 2nd argument with:
> >> >> >
> >> >> > If Not IsNull(brSecondaryKeyValue) Then
> >> >> >
> >> >> >This is executing as if there is a value in brSecondaryKeyValue. How do I
> >> >> >clear this value? Do I set the Object (it's not an object), or make it equal
> >> >> >to zero or null?
> >> >>
> >> >>
> >> >> In addition to Duane's explanation of using the arguments
> >> >> default value, if you can live with declaring the optional
> >> >> argument as Variant, then you could use the IsMissing
> >> >> funtion.
> >> >>
> >> >> --
> >> >> Marsh
> >> >> MVP [MS Access]
> >> >>
> >>
> >> --
> >> John Nurick [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>