Passing Optional Arguments to Function

[Mark Maeker]

I have setup the function shown below. I am trying to use
it in a query as shown
Balance: ReceivableInvoiceBalance(InvoiceID, ,PaymentID)

I get the following error message:
The expression you entered contains invalid syntax;
You may have entered a comma without a preceding value or
identifier.

The problem is that it will not let me omit the second
argument.

Public Function ReceivableInvoiceBalance(lngInvoiceID As
Long, Optional datEndingDate As Date, Optional
lngPaymentID As Long) As Currency
On Error GoTo FunError

 

[Marshall Barton]

I have setup the function shown below. I am trying to use
it in a query as shown
Balance: ReceivableInvoiceBalance(InvoiceID, ,PaymentID)

I get the following error message:
The expression you entered contains invalid syntax;
You may have entered a comma without a preceding value or
identifier.

The problem is that it will not let me omit the second
argument.

Public Function ReceivableInvoiceBalance(lngInvoiceID As
Long, Optional datEndingDate As Date, Optional
lngPaymentID As Long) As Currency
On Error GoTo FunError
.
.
.
FunExit:
Exit Function

FunError:
MsgBox Err.Description
Resume FunExit

End Function

Can anyone tell me what I am doing wrong or if this is a
BUG in Access?

In my limited experience using optional arguments in the SQL
environment, missing arguments can only be at the end of the
argument list.

I only tried to do this once and got around the error you're
seeing by coding the function to test for Null as well
Missing. Then I could call the function this way:

ReceivableInvoiceBalance(InvoiceID, Null, PaymentID)

to get the desired result.

 

[Mark Maeker]

-----Original Message-----

I have setup the function shown below. I am trying to use
it in a query as shown
Balance: ReceivableInvoiceBalance(InvoiceID, ,PaymentID)

I get the following error message:
The expression you entered contains invalid syntax;
You may have entered a comma without a preceding value or
identifier.

The problem is that it will not let me omit the second
argument.

Public Function ReceivableInvoiceBalance(lngInvoiceID As
Long, Optional datEndingDate As Date, Optional
lngPaymentID As Long) As Currency
On Error GoTo FunError
.
.
.
FunExit:
Exit Function

FunError:
MsgBox Err.Description
Resume FunExit

End Function

Can anyone tell me what I am doing wrong or if this is a
BUG in Access?

In my limited experience using optional arguments in the SQL
environment, missing arguments can only be at the end of the
argument list.

I only tried to do this once and got around the error you're
seeing by coding the function to test for Null as well
Missing. Then I could call the function this way:

ReceivableInvoiceBalance(InvoiceID, Null, PaymentID)

to get the desired result.

--
Marsh
MVP [MS Access]
.
That will work.

Thank-you!