Use of "Optional" parameters

[GraeagleBill]

I created a function:

Public Function eBudAcctBal(TableName As String, Optional ID As Variant = Null, Optional Dte As Variant = Null) As Currency

In the module's code stream there's an IF statement:

If IsMissing(ID) And IsMissing(Dte) Then
    strQuery = strQuery & QALL

That does not recognize the missing parameters and skips over the strQuery assignment when the function is being tested thus:

Msgbox eBudAcctBal("tblBankSavings")

My understanding about the use of the IsMissing function is that it applies ONLY to Variant parameters. "I" seem to be missing something???

[Micron]

You've assigned Null to the parameter if it isn't provided, thus it isn't missing. You should be able to prove this (right or wrong) by stepping through and validating the value of the parameter when it hasn't been provided.

[GraeagleBill]

My understanding is that Optional parameters "Must" include a default value. The 2nd parameter is easy, as I could make it "Integer = 0" and test for 0 in determining if it's missing or 0 was actually passed. The 3rd parameter is a date, but Access wouldn't allow "As Date = Date()". I started with "As Variant = NULL" in both cases but in some cases, like "Case" options Access does not like "Case IsNull".

[Micron]

No, if the parameter had to have a default value, then it wouldn't be optional. Maybe this will help
https://support.office.com/en-us/art...0-7691a3944bf1

Note especially the calls to ReturnTwice function:
ReturnValue = ReturnTwice() <<< optional but no value
ReturnValue = ReturnTwice(2) <<< optional, but 2 passed to function

For the rest of that paragraph on default values, if you don't need them, don't try to pass them. Alternatively, validate that a parameter isn't missing before calling the function or provide the defaults there.

Access does not like "Case IsNull".

If memory serves, that was a post of yours recently. Then use IF blocks to test for Null.

[GraeagleBill]

I read your reference earlier and right or wrong got the idea that the Function statement necessarily had to use Variant if I was to use IsMissing to detect what was passed, or not. I think my code will be much easier to follow if I use special values like what was offered in your reference:

Sub MySub(Optional MyVar As String = "specialvalue")
    If MyVar = "specialvalue" Then
        ' MyVar was omitted.
    Else
    ...
End Sub

[Micron]

got the idea that the Function statement necessarily had to use Variant

It does. This is the first time you've said that, no? What it doesn't have to contain is default values. That's another thing altogether. Why not just

Public Function eBudAcctBal(TableName As String, Optional ID As Variant, Optional Dte As Variant) As Currency
If (IsNull(ID) And IsNull(Dte)) Then strQuery = strQuery & QALL

As long as ID and Dte cannot contain empty strings ("") then testing for Null should be sufficient.

[GraeagleBill]

Got it. As soon as I find a syntax error in one of the WHERE expressions I'll switch to what you've suggested. I think in this case maybe "NULL" is my friend

[GraeagleBill]

With the Function statement as you suggested:

Public Function eBudAcctBal(TableName As String, Optional ID As Variant, Optional Dte As Variant) As Currency

The use of "IsNull" will result in a code path taken where the "missing" parameter will get referenced "by name" resulting in datatype error. What is required to properly test parameters is "IsMissing". The following code block processes perfectly. (BTW, the code is preparing a SELECT string in preparation for updating a QueryDefs so I can get JET to do all the needed calculations work.)

If IsMissing(ID) And IsMissing(Dte) Then
    strQuery = strQuery & QALL
Else
    If Not IsMissing(ID) And IsMissing(Dte) Then
        strQuery = strQuery & QID
        strQuery = Replace(strQuery, "IDExp", ID)
    Else
        If IsMissing(ID) And Not IsMissing(Dte) Then
            strQuery = strQuery & QAllDated
            strQuery = Replace(strQuery, "dteExp", Dte)
        Else
            If Not IsMissing(ID) And Not IsMissing(Dte) Then
                strQuery = strQuery & QIdDated
                strQuery = Replace(strQuery, "IDExp", ID)
                strQuery = Replace(strQuery, "dteExp", Dte)
            End If
        End If
    End If
End If

[Micron]

When dealing with multiple logical operators I strongly suggest to use grouping that reflects the manner in which you want them to be evaluated. Assume that your code receives ID missing, Dte missing. Access could potentially evaluate as (all caps replaces expression or function being evaluated).

Without grouping the potential is
If Not IsMissing(ID) And IsMissing(Dte)
IF NOT TRUE AND TRUE (simplfy to):
IF FALSE AND TRUE

With grouping
If Not (IsMissing(ID) And IsMissing(Dte))
IF NOT (TRUE AND TRUE)

Will Access evaluate
IF (Not True) And True
or
IF (Not (True and True))? That is the chance you're taking. I would suggest you test with the 4 (I think) variations of those 2 parameters and see how the IF blocks are evaluated.