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,"")

"LAS" <(E-Mail Removed)> wrote in
news:i5125e$qmc$(E-Mail Removed):

> 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

--
Jack D. Leach

--
Jack D. Leach

"Jack D. Leach" <(E-Mail Removed)> wrote in
news:Xns9DDE96B9B4136dymondjackathotmailc@81.169.183.62:

To further the discussion on passing values that may be Null to a procedure
parameter, one generally uses the Nz function on the calling side to make
sure the Null isn't passed, but instead a default value...


Value = SomeFunction(Nz(Me.TextControl, ""), Nz(Me.NumericControl, 0)

Function SomeFunction(sVal As String, lVal As Long)
If Len(sVal) Then
'strng value was passed
End If
If lVal Then
'numeric value was passed
End If
End Function


--
Jack D. Leach

"LAS" <(E-Mail Removed)> wrote in
news:i5125e$qmc$(E-Mail Removed):

> 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,"")

Your code is not checking for Null, so it won't work.

My principle is this:

For functions that get called in rows of a SQL statement, if the
fields you're passing them can be Null, then use variant data types.

For all other subs/functions, I don't use variant data types except
if the data is variant (which is pretty uncommon and not that easy
to deal with).

I often use functions in rows of a SQL statement that are passed
fields that can be Null, but the problems can be avoided by
concatenating the field value with an empty string or using Nz().

Basically, I avoid variants because they are somewhat unpredictable
and because it violates the rule of strong data typing, which I
consider more important than mere convenience. That means that when
using them, I have to think about the data I'm passing them and act
accordingly.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Oh, hey! That "optional" is a nugget of gold! Thanks.

"Jack D. Leach" <(E-Mail Removed)> wrote in message
news:Xns9DDE96B9B4136dymondjackathotmailc@81.169.183.62...
> "LAS" <(E-Mail Removed)> wrote in
> news:i5125e$qmc$(E-Mail Removed):
>
>> 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
>
> --
> Jack D. Leach
>
> --
> Jack D. Leach

Thanks for the extensive info. Have removed variants from my parameters!!

"Jack D. Leach" <(E-Mail Removed)> wrote in message
news:Xns9DDE96B9B4136dymondjackathotmailc@81.169.183.62...
> "LAS" <(E-Mail Removed)> wrote in
> news:i5125e$qmc$(E-Mail Removed):
>
>> 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
>
> --
> Jack D. Leach
>
> --
> Jack D. Leach

"LAS" <(E-Mail Removed)> wrote in
news:i51an6$539$(E-Mail Removed):

> Thanks for the extensive info. Have removed variants from my
> parameters!!
>


Glad to help. The Optional statement is useful in a lot of cases, but
personally I try to avoid it for the reason of specifically avoiding Nulls
(try to only use optional if the argument is in fact optional, and the
function returns or performs correctly with a default value). Also note
that the Optional statement doesn't need have a default value set as per my
last post. In some cases, you can use IsMissing to check if an optional
argument has been passed, but this is almost as bad to figure out as
handling Nulls (read the help files thoroughly on IsMissing before you try
to use it, there's a number of caveats).

Generally speaking, if the argument isn't actually Optional, I prefer to
trap Nulls on the calling side instead of the function side. There are
times though, as David mentioned, that it isn't an option and the Variants
and Optionals are helpful.

Cheers,

--
Jack D. Leach

"LAS" <(E-Mail Removed)> wrote in
news:i518lk$rs3$(E-Mail Removed):

> That "optional" is a nugget of gold!

While it's great to learn about optional arguments (one thing you
have to know is that in cases with more than one argument in the
declaration, the optional arguments have to come at the end, with no
required arguments after them), this doesn't have anything at all to
do with the question you asked. It doesn't resolve the question of
handling Null arguments or the use of variant type in parameters.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/