[dba-Tech] On Odious Code

Hi Stuart

At least in Access 97 it doesn't fail, it just returns VarType 0 which
is - surprise - Empty. And who say Null is the requested return value
for an unknown value?

Further, you can easily deal with an empty if you have to:

  varEmpty & Null => Empty
  varEmpty + Null => Null
  varEmpty & "xy" => "xy"
  varEmpty + "xy" => "xy"
  varEmpty & 6789 => "6789"
  varEmpty + 6789 => 6789 (number)

Thus, as you probably wish to have a return value of a specific type,
the use of the plus sign will be the general approach:

  varValue = varEmpty + Null
  strValue = varEmpty + vbNullString
  lngValue = varEmpty + 0
  intValue = varEmpty + 0
  curValue = varEmpty + 0
  booValue = varEmpty + False

and so on for the remaining numeric types.
For date/time there is no "neutral" value; you will have to decide
yourself, for example:

  datValue = varEmpty + Now()
  
For your function, you can check if you have a stored value:

  If IsEmpty(StoredVariable) = True Then
    ' No stored value
  End If

This also answers Arthur's question:

IsEmpty() checks if a variable of type Variant has not been assigned
any value including Null. This is useful as a variant carries no
default value as variables of other types do.

For a variable, varValue, of type Variant the general test is:

  If IsEmpty(varValue) = True Then
    ' No value including Null has yet been assigned to varValue.
  End If

/gustav


>> And while we're on it, would someone kindly provide examples of the
>> correct use of IsEmtpy()? This one has always baffled me.

> Here's one. Without the "If IsEmpty()..." line, this function returns an error 
> if called for the first time with no varInput:

> Static Function StoredVariable(Optional varInput As Variant) As Variant
>     Dim varStore As Variant

>     'Initialise the variant if the function is called
>     'the first time with no Input
>     If IsEmpty(varStore) Then varStore = Null

>     'Update the store if the Input is present
>     If Not IsMissing(varInput) Then varStore = varInput

>     'return the stored value
>     StoredVariable = varStore
> End Function