I never got back to this, but I fully discourage anyone from using the ismissing function and that is why I believe Microsoft has shown at least some interest in deprecating it. The problem is the ismissing function is going to get you in trouble. Even someone as skilled as Remou demonstrated the problem
It is very useful for dates for example, where nearly any default value could be valid.
In contrast it has no utility here and will get you in trouble. Ismissing ONLY works with VARIANTS. It is only the variant that carries the missing bit. It is confusing in what it will provide, and here is where you will get in trouble.
Code:
Public Sub badIsMissing(Optional dtmDate As Date)
MsgBox IsMissing(dtmDate)
If Not IsMissing(dtmDate) Then
'your code here
End If
End Sub
Public Sub testBadIsMissing()
Call badIsMissing
End Sub
What does the message box display? True or false? I did not pass any parameter so you would think True. But as Remou pointed out since I declared it as a date it defaults to 0 or (12:00AM 31Dec 1899). isMissing returns FALSE. That is obviously not what was intended, and likely to cause really bad things if used in a query.
So to use it correctly the parameter needs to be declared as a variant, and now the second problem. Why waste your time checking if it is missing? You need to check if it is a date. (Unless if it is missing you do one thing, and if it is a non date something else)
Code:
Public Sub badIsMissing2(Optional dtmDate As Variant)
Dim x As Date
If Not IsMissing(dtmDate) Then
x = dtmDate
End If
End Sub
Public Sub testBadIsMissing2()
Call badIsMissing2("XYX")
End Sub
It sees that it is not missing so that means I have passed in a valid date. Wrong it crashes
So bottom line ismissing has really no utility and eventually it will bite you. I strongly believe in the following for any function used in a query.
1) If the function is going to be used in a query ALWAYS declare the parameters as variants, contrary to what you learned in coding. Why? Because eventually you are going to pass in a null value, and it will crash your query. Nothing is more frustrating then 10,000 records with error messages. Either you will use the function with a new record, or you will forget to assign a value. Even if in your table you have default values and required values, some day you will do an outer join and return null values. I am not suggesting this for all functions only those used by queries. For other programs you should declare the most specific data type.
2) Since you should always declare the parameters as variants for functions called by queries, you should then ALWAYS assign a default for all optional parameters.
3) Then in the function if it is supposed to be a date check that, if it is supposed to be numeric check that, if it is supposed to be a string check not null.
The following construct always works and is never confusing.
Code:
Public Sub goodConstruct(Optional dtmValue As Variant = Null, Optional strValue As Variant = Null, Optional numValue As Variant = Null)
If IsDate(dtmValue) Then
End If
If IsNumeric(numValue) Then
End If
If Not IsNull(strValue) Then
End If
End Sub
So the above construct never bombs out when your query passes in a null which it will eventually do. If you pass in a real date it handles that, it handles no value passed in, and it handles a non date correctly. Same for numeric and string.
And if you want to provide other defaults then null you can handle that as well
optional numValue as variant = 0
if numValue <> 0
Bottom line. If using optional paramters ALWAYS declare as variants and always provide default values. Avoid ismissing.
