Avoiding type conflict

[Guest]

Hello,
I have the situation where I need to read a table field value, which can
be of any data type, and convert that to a string. To do this I pass the
field value to a function expecting a Variant parameter and decide on the
best string conversion function after testing the subtype of the Variant
using VarType(). A problem arises when I try to pass that Variant onwards to
my home-brewed function which converts a Boolean parameter to a string like
"YES" or "NO". The compiler does not like this, saying "ByRef argument type
mismatch". If I add the word "ByVal" in front of the Boolean parameter, it
all works, but I don't see how simply using a copy of the Variant resolves
the type mismatch. Can anyone advise me on what's hapening or a better
approach?
Thanks,
Dave.

 

[Allen Browne]

If the field contains no entry, the value will be Null.

Variant is the only VBA type that can have the Null value, so if you may be
passing in a Null, your function must accept a variant.
Function MyFunc(var1 As Variant) As String
You can then use Nz() to convert the null into False or "NO" or whatever you
wish.

Nulls are a very important facet of database theory and practice. If this is
not familiar territory, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html