I'm trying to come up with a standard way to deal with the problem
that some things allow nulls and others don't, and it's taxing my
brain to try to keep them straight. I'm thinking about using variants
in all my function parameters, and dealing with them, depending on
need, something like I have below. Are there downsides to this?
Public Function fncTotalByOther(av_CodeGroup As Variant, av_Where As
Variant) As String
On Error GoTo Err_TotalByOther
fncTotalByOther = "Bad data"
If Nz(av_CodeGroup) = "" Then
MsgBox ("fncTotalByOther: Code_Group is empty")
Exit Function
End If
av_Where = Nz(av_Where,"")
Hi,
That's not a very good idea (no offense), for many reasons. The biggest
reason is that a variant can anything, and in any case that VBA finds a
variant, it will do it's best to try and guess what it is "supposed" to
be (you don't ever really want access or VBA to decide what it should on
your behalf). To get around this, you need to use explicit type
conversion on everything you do (CStr, CLng, CBool etc), and that is a
real pain for sure.
Variants are also the most expensive datatype there is when it comes to
memory, but that's really besides the point when compared to the fact
that variants are an absolute nightmare when it comes to making sure
they're being handled how they're supposed to be handled. Debugging the
problems that comes with variants is as much more of a nightmare to
regular datatypes as using type conversions all over the place is
compared to "regular" coding.
This isn't to say that Variant's don't have their place, but they should
be used with extreme care. In most of my projects, out of 100 variables
I declare, maybe one of them is variant.
As a rule, we should always stay away from the variant datatype without
explicit need (this need usually traces to automation or a parameter
that can, for instead, take either a string or numeric ID to a function,
which then needs to be explicitly checked for it's containing datatype
and (should be) converted before further use).
Dealing with Nulls is sometimes daunting, true... but the Nz and IsNull
functions are our saviors here. When working with parameters of a
function, you can make them optional with a default value instead of
using a variant. Example:
Function DoSomething(Optional Val As String = "")
If Len(Val) = "" Then
MsgBox "No Val passed"
End If
End Function
hth