Null Values

[Dan McClelland]

Using SQL Server 2K back end and Access XP project as
front end.

Have a bound form with bound textbox controls. Want the
FirstName and LastName fields to begin with Null values
upon creating a new record.

* Set the DefaultValue property of the fields to Null in
table design mode. Doesn't work, the fields initialize
with empty strings ("").

* Set the DefaultValue property of the textbox controls to
Null. Still doesn't work and they still initialize with
values of empty strings.

* Tried to programmatically set the values of the textbox
controls to Null as part of the Click event code for my
New Record button. Doesn't work, I get error "You tried
to assign a Null value to a variable that is not a Variant
data type." HUH? What variable. I'm only dealing with a
textbox control bound to an nvarchar field.

What other resources are there to get assistance with
these seemingly undocumented, and illogical hiccups
between SQL Server and Access? I'm redesigning an
existing Access 97 database as an Access XP adp using SQL
Server 2000 data, and things are suddenly not working as
expected.

 

[Jeff Boyce]

Dan

You explained what, but not why. Given that your users will not be able to
tell the difference between a Null and a zls ("") in an empty textbox on
your form, what is your underlying need to force nulls?

I've noticed that I am unable to force a null into a SQL-Server column
(field) if the definition does not include "Nullable". Is that an issue in
your situation?

Good luck

Jeff Boyce
<Access MVP>

 

[TC]

You are maybe confusing "controls" and "fields".

When you create a new record in Jet - and doubtless also in SQL Server - a
text field like forname will be Null, unless a default value is defined for
that field.

But text >controls< (like textboxes) on Access forms, are Text items - not
Variants - so they do not support Null values.

When you display a Null valued field in a textbox control, the control
displays blank. The content of the control is an empty string. The Null has
been converted to an empty string for display purposes.

When Access >saves< a textbox value, it strips trailing spaces (if any). If
the result is an empty string, it converts it back to Null for storage in
the table field.

So, Nulls in the table, equate to zero-length strings in the textbox
control - with Access converting back & forth, automatically, whenever
required.

If you create a new record in an Access form, & leave a textbox (that is
bound to a table field) blank, that will store a Null in that field - not a
zero length string.

HTH,
TC