Assign default value to Optional Variable

[Guest]

Hi,

How can I assign a default value to an Optional Variable Argument in a
function, specially if it is a boolean ?

The following is a sample function written to highlight my problem. I want
the AddMiddleName argument to be optional, and and should be set to True, if
avoided.
Thanks for any tips on this.

Public Function FullName(strCustomerID As String, _
Optional AddMiddleName As Boolean) As String
On Error GoTo ErrHandle

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strWhere As String
Dim BuildName As Variant

strSQL = "SELECT Title, FirstName, MiddleName, LastName FROM tblCustomer"
strWhere = "CustomerID = '" & strCustomerID & "'"
strSQL = strSQL & " WHERE " & strWhere

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
If Not .EOF Then
BuildName = !Title
BuildName = BuildName & ". " & !FirstName
If AddMiddleName Then BuildName = BuildName & " " + !MiddleName
BuildName = BuildName & " " + !LastName
End If
End With

FullName = BuildName

Set db = Nothing
rs.Close
Set rs = Nothing

ExitHandle:
Exit Function

ErrHandle:
LogError "FullName" & conMod, Err, Error
Resume ExitHandle

End Function

 

[RoyVidar]

Hi,

How can I assign a default value to an Optional Variable Argument in
a function, specially if it is a boolean ?

The following is a sample function written to highlight my problem. I
want the AddMiddleName argument to be optional, and and should be
set to True, if avoided.
Thanks for any tips on this.

Public Function FullName(strCustomerID As String, _
Optional AddMiddleName As Boolean) As String
On Error GoTo ErrHandle

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strWhere As String
Dim BuildName As Variant

strSQL = "SELECT Title, FirstName, MiddleName, LastName FROM
tblCustomer" strWhere = "CustomerID = '" & strCustomerID & "'"
strSQL = strSQL & " WHERE " & strWhere

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
If Not .EOF Then
BuildName = !Title
BuildName = BuildName & ". " & !FirstName
If AddMiddleName Then BuildName = BuildName & " " +
!MiddleName BuildName = BuildName & " " + !LastName
End If
End With

FullName = BuildName

Set db = Nothing
rs.Close
Set rs = Nothing

ExitHandle:
Exit Function

ErrHandle:
LogError "FullName" & conMod, Err, Error
Resume ExitHandle

End Function

Did you try something like this?

Public Function FullName(strCustomerID As String, _
Optional AddMiddleName As Boolean = True) As String

 

[Allen Browne]

You can specify a value to use for an optional argument:

Public Function FullName(strCustomerID As String, _
Optional AddMiddleName As Boolean = True) As String

 

[Brendan Reynolds]

Just add "= DefaultValueHere" after the argument declaration. Here's an
example ...

Public Function OptionalWithDefault( _
arg1 As String, _
Optional arg2 As Boolean = True, _
Optional arg3 As String)

OptionalWithDefault = arg1 & "/" & CStr(arg2) & "/" & arg3

End Function

And here's an example of use, in the Immediate window ...

? optionalwithdefault("one",,"two")
one/True/two

 

[Guest]

Hi all,

I can't believe it's that simple !!!

Thank you very much.