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().

 

[Gary Schuldt]

Yeah, I posed a sloppy question but got the answer I needed . . . thanks!

I did know that the default type was Variant.

Good tip on vbNullString.

My function's logic is more complex than what I suggested in my post--i.e.,
I need to test 3 fields individually for "anything useful there" before I
start concatenating, so I'm thinking that I'll Nz them all at the outset so
I can just test for null string and be done with it. The volume's so low in
this case that performance is not an issue.

That takes me back to a general question, though: If I were writing a
general routine and wanted to protect myself from null-triggered errors and
was only interested in a field that was non-blank with no leading or
trailing blanks . . . should I then do a Trim(Nz(field))? Or is there
something better?

(We got 5.3" of rain today. blurp glub bloop)

Gary

 

[Dirk Goldgar]

Yeah, I posed a sloppy question but got the answer I needed . . .
thanks!

I did know that the default type was Variant.

Good tip on vbNullString.

My function's logic is more complex than what I suggested in my
post--i.e., I need to test 3 fields individually for "anything useful
there" before I start concatenating, so I'm thinking that I'll Nz
them all at the outset so I can just test for null string and be done
with it. The volume's so low in this case that performance is not an
issue.

That takes me back to a general question, though: If I were writing a
general routine and wanted to protect myself from null-triggered
errors and was only interested in a field that was non-blank with no
leading or trailing blanks . . . should I then do a Trim(Nz(field))?
Or is there something better?

Yes, that will work. I lean toward using concatenation with
vbNullString rather than using Nz(), but it amounts to the same thing in
the end. I can't remember if I ever did performance testing to see if
one is more efficient than the other.

(We got 5.3" of rain today. blurp glub bloop)

Yikes! Where do you live?

 

[Gary Schuldt]

I live in the Seattle / Puget Sound area . . . Olympia, the state capitol!
Am still working on the plant labeling project and just did a post in the
VBA NG about Alter Table . ..

Gary