Optional parameter in Sub Procedure

[Guest]

Can anyone tell me why when I pass no date parameter to this function the Len
enumerates to 8 and not zero?

Private Sub AuditAdd(strStuff As String, Optional dteDate As Date)
On Error GoTo ER
Dim dteNow As Date
If Len(dteDate) = 0 Then
dteNow = Now()
Else
dteNow = dteDate
End If

Thanks.

 

[Guest]

I had the same problem one time. So the following is what I found out......
your milage may vary.....


"dteDate" is defined as a "Date Data Type". From Help:

"Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers
that represent dates ranging from 1 January 100 to 31 December 9999 and
*times* from 00:00:00 to 23:59:59. <snip>"

Since you are not passing a date/time as an argument, it defaults to 0
(zero). A date variable of zero is the same as a time of 12:00:00AM, which is
00:00:00, a length of 8.

One way to check if the a date has been passed is to convert "dteDate" to a
Single data type using the CSng() function and check for a value of zero.

.............but I could be wrong

HTH

 

[Albert D. Kallal]

When you use the "len" command on a non string, you get the length of the
data in bytes.

Dim dt As Date
Dim lng As Long
Dim dbl As Double

Debug.Print Len(dt)
Debug.Print Len(lng)
Debug.Print Len(dbl)

output:
8
4
8

Further, do be aware that you an use the ismssing() function to check for
missing parameters. This function ONLY WORKS if you use a variant:

so:

Private Sub AuditAdd(strStuff As String, Optional dteDate As Varient)

if ismissing(dteDate) = true then
date not passed code goes here..

 

[Guest]

I don't suppose a procedure has a collection for parameters?
It seems there should be a better way...