Representing a null text field value in VBA

[Guest]

Hi,
I have a table with a text field which allows zero-length strings. For
my Access 97 task at hand I need to compare that value with a similar field
in another table. If they are different, I copy both values to a third table.
This is part of a VBA module.
My questions is: if a field's value can be null, is that a value that
can be represented by a string variable in VBA?
From my reading/experimentation, simply assigning a field with a value
of null to a string variable causes an error (#94). Is it therefore the case
that I have to test the field's value first with IsNull() and if true,
'copying' its value to my third table means doing nothing (as new records
there will contain null fields by default)?
Thanks,
Dave.

 

[John Nurick]

Hi Dave,

It's a little more complicated.

A variable declared As String can only contain string values, and a Null
is by definition not a string (or anything else except a Null). If you
need a variable that *can* be Null, you have to declare it As Variant.

Also, when a new record is created, its fields are not necessarily Null.
They get whatever default value was set when you built the table or - if
the new record is being created by a form bound to [a query on] the
table - the default value of the control they are bound to.

So you can either fiddle with IsNull() and make sure the default values
are Null - or just transfer your value in a Variant variable.

 

[Guest]

Thanks mate.

Hi Dave,

It's a little more complicated.

A variable declared As String can only contain string values, and a Null
is by definition not a string (or anything else except a Null). If you
need a variable that *can* be Null, you have to declare it As Variant.

Also, when a new record is created, its fields are not necessarily Null.
They get whatever default value was set when you built the table or - if
the new record is being created by a form bound to [a query on] the
table - the default value of the control they are bound to.

So you can either fiddle with IsNull() and make sure the default values
are Null - or just transfer your value in a Variant variable.

Hi,
I have a table with a text field which allows zero-length strings. For
my Access 97 task at hand I need to compare that value with a similar field
in another table. If they are different, I copy both values to a third table.
This is part of a VBA module.
My questions is: if a field's value can be null, is that a value that
can be represented by a string variable in VBA?
From my reading/experimentation, simply assigning a field with a value
of null to a string variable causes an error (#94). Is it therefore the case
that I have to test the field's value first with IsNull() and if true,
'copying' its value to my third table means doing nothing (as new records
there will contain null fields by default)?
Thanks,
Dave.