String vs. Variant data type

[Gary Schuldt]

I am manipulating some fields that are declared as Text in the table in
which they occur. I notice that sometimes you MVPs use String variables and
other times Variant.

Is there some reason to prefer the more general Variant over String when you
*know* you're dealing only with Text data? I guess I'm assuming that the
field data type of "Text" equates to the VB type of "String". I'm also
guessing that there is a difference in handling of nulls between the two.

I'd appreciate any light you can shed on the subject.

Thanks.

Gary

 

[Scott McDaniel]

The Variant datatype would be the least preferred datatype, but often it is
the only datatype that is appropriate. Variants are often used as datatypes
for parameters passed into functions and such, simply because you never know
when someone will try to pass a null value to it. A Variant-tye variable is
expensive in terms of overhead, since the system doesn't know how to handle
it. And, make sure to properly type your return variable. If you KNOW that
the values passed in will NOT contain nulls (IOW, validate your data before
passing it to functions, queries, etc), then you should declare you
variables as the proper type.

 

[Michel Walsh]

Hi,


Only a Variant can handle a Null value. A variant is also the datatype
of Fields("fieldName").Value and of a Control.Value. A Null may be seen
as a value not available, an unknown value. They may be present as data, or
pop out as outer join (in the unpreserved side of the join).


The control Text property, in Access, refers to what the user is typing,
as it is modified. It is not "committed" (validated) at any kind of level
by Access, yet.




Hoping it may help,
Vanderghast, Access MVP

 

[Gary Schuldt]

Scott,

OK, what you say makes sense: There has to be run-time code to determine
the actual type of a Variant type before operations can be performed, and
that's why it's more expensive to use it.

Let me get specific: Suppose I have a String function which concatenates
its three parameters together to get the output string:

fcnBuildString (a, b, c) As String

fcnBuildString = a & b & c
End

Let's also suppose that a,b,c could be non-zero length strings, zero-length
strings, or null.

Are you saying that the assignment statement WOULD work as written, with no
additional validation, if a, b, and c were declared as Variant?

If not, then do I need to use NZ to convert them all to strings before
executing that statement?

I'm just confused on how much validation / conversion I need to do within a
function when I want to deal with its parameters as strings (assuming that
no programmatic validation has been done prior to invoking the function).

Gary

 

[Gary Schuldt]

Does that mean that IsNull (stringVar) is never True?

The "Text" I'm referring to is the datatype you specify when defining a
field in a table. If it's properties don't specify a default value, and a
value is not required when you create a row in this table, I understand that
IsNull(Record.textField) will be True if no data has been entered into it.

Thanks.

Gary

 

[Albert D. Kallal]

You are correct.

so, in code where you deal with fields/data can be null, then often you will
use Variant types in place of string types.

So, a good rule is that if you can, or expect data to be null in the record,
then if your code needs to allow null values also, then you must use
variants.

If your current code you are writing does NOT have to allow, or deal with
null values, then use a string type.

 

[Gary Schuldt]

Thanks, Albert.

Learning this way is less painful (and faster) than making more dumb
mistakes than I need to.

Gary

 

[Dirk Goldgar]

Let me get specific: Suppose I have a String function which
concatenates its three parameters together to get the output string:

fcnBuildString (a, b, c) As String

fcnBuildString = a & b & c
End

Let's also suppose that a,b,c could be non-zero length strings,
zero-length strings, or null.

Are you saying that the assignment statement WOULD work as written,
with no additional validation, if a, b, and c were declared as
Variant?

As written, a, b, and c *are* declared (implicitly) as Variant.
However, this function would fail if all three of the arguments are
Null, because then the result of the concatenation would be Null, and
the function returns a String value, which can't be Null. You could fix
that problem by changing it like this:

fcnBuildString (a, b, c) As String

fcnBuildString = a & b & c & vbNullString

End

By adding the concatenation to a string value, you force the result of
the expression to a String -- a zero-length string -- and get the proper
return type.

If not, then do I need to use NZ to convert them all to strings before
executing that statement?

It's convenient to know that concatenating Null to String yields String.
That can save time over calling Nz().