Bug in Access?

[n00b]

I have some text fields in several linked SQL Server 2000 tables that
intermittantly have either a zero length string placed in the field or
a null value. My desire is to have null values when there isn't any
data entered into the field from an Access 2003 form (Access 2000 file
format is what we are using however.) I checked the link itself and
it has "AllowZeroLengt h" by default set to Yes and "Required" by
default set to No (I have not modified any properties of the link.)
According to MS documentation, this is what I want (even though the
user could still type "" to force an empty string.) I know without a
shadow of a doubt the user doesn't know how to do this and for
simplicity I leave the link untouched with the default settings. The
fields themselves are all varchar data types, with "Allow Nulls"
checked in the table designer of the Enterprise Manager, and default
values not present.

Two things in my mind could be the reason, I either only "think" the
user doesn't know how to enter "" to get an empty string or its
bugged. Could anyone please shed some light on this for me? I am
totally baffled.

[Tom van Stiphout]

Re: Bug in Access?

On 30 Jan 2007 09:09:41 -0800, "n00b" <advinn@email.c omwrote:

You rarely ever would want to allow both null and ZLS in the same
field, especially if they kind-of mean the same thing. In SQL Server
it's easy to put a Check Constraint to avoid the problem.

The Access link information doesn't mean much to me; I would
exclusively look at what the SQL Server table allows.

How you got ZLS in your field is anyone's guess, not knowing your app.
One possibility is that the user enters <space>, which Access in its
infinite wisdom may truncate to a ZLS.

-Tom.

>I have some text fields in several linked SQL Server 2000 tables that
>intermittant ly have either a zero length string placed in the field or
>a null value. My desire is to have null values when there isn't any
>data entered into the field from an Access 2003 form (Access 2000 file
>format is what we are using however.) I checked the link itself and
>it has "AllowZeroLengt h" by default set to Yes and "Required" by
>default set to No (I have not modified any properties of the link.)
>According to MS documentation, this is what I want (even though the
>user could still type "" to force an empty string.) I know without a
>shadow of a doubt the user doesn't know how to do this and for
>simplicity I leave the link untouched with the default settings. The
>fields themselves are all varchar data types, with "Allow Nulls"
>checked in the table designer of the Enterprise Manager, and default
>values not present.
>
>Two things in my mind could be the reason, I either only "think" the
>user doesn't know how to enter "" to get an empty string or its
>bugged. Could anyone please shed some light on this for me? I am
>totally baffled.

[Gord]

Re: Bug in Access?

>How you got ZLS in your field is anyone's guess, not knowing your app.

>One possibility is that the user enters <space>, which Access in its
>infinite wisdom may truncate to a ZLS.

I suspected that too, recalling how often users will "empty" a cell in
Excel by hitting the spacebar and then wonder why it doesn't act like
an empty cell. However, Access itself doesn't convert ' ' to ''. I
opened a SQL server linked table in spreadsheet view, typed a space
and saved the record, and the column was <NULLwhen I checked it on
the SQL server. It is possible that some bound controls may behave
differently, I suppose....


On Jan 30, 11:26 pm, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:

On 30 Jan 2007 09:09:41 -0800, "n00b" <adv...@email.c omwrote:
>
You rarely ever would want to allow both null and ZLS in the same
field, especially if they kind-of mean the same thing. In SQL Server
it's easy to put a Check Constraint to avoid the problem.
>
The Access link information doesn't mean much to me; I would
exclusively look at what the SQL Server table allows.
>
How you got ZLS in your field is anyone's guess, not knowing your app.
One possibility is that the user enters <space>, which Access in its
infinite wisdom may truncate to a ZLS.
>
-Tom.
>

I have some text fields in several linked SQL Server 2000 tables that
intermittantly have either a zero length string placed in the field or
a null value. My desire is to have null values when there isn't any
data entered into the field from an Access 2003 form (Access 2000 file
format is what we are using however.) I checked the link itself and
it has "AllowZeroLengt h" by default set to Yes and "Required" by
default set to No (I have not modified any properties of the link.)
According to MS documentation, this is what I want (even though the
user could still type "" to force an empty string.) I know without a
shadow of a doubt the user doesn't know how to do this and for
simplicity I leave the link untouched with the default settings. The
fields themselves are all varchar data types, with "Allow Nulls"
checked in the table designer of the Enterprise Manager, and default
values not present.

>

Two things in my mind could be the reason, I either only "think" the
user doesn't know how to enter "" to get an empty string or its
bugged. Could anyone please shed some light on this for me? I am
totally baffled.