Passing Optional Arguments to Function

  • Thread starter Thread starter Mark Maeker
  • Start date Start date
M

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
 
Mark said:
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?
Click to expand...

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.
 
-----Original Message-----
Mark said:
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?
Click to expand...

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.
Click to expand...
Thank-you!
 
Back
Top