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 "AllowZeroLength" 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.

 

[SusanV]

As soon as a text box is populated it is no longer Null - if the user then
deletes the text they typed it becomes a zero-length string. Why do you need
nulls? For a query? If so, modify the query to check for Null OR ""

 

[n00b]

As soon as a text box is populated it is no longer Null - if the user then
deletes the text they typed it becomes a zero-length string. Why do you need
nulls? For a query? If so, modify the query to check for Null OR ""
--
hth,
SusanV







- Show quoted text -

What you are saying can't be true. When I delete the contents of a
textbox bound to the SQL server table field I described, it saves null
in all tests I have tried. At least on my workstation this is true.
Could this be a workstation specific issue? I can't make a ZLS save
to the field in any of my tests, yet the database doesn't lie, the
problem is there.

The reason I'm asking this question is because we have about 50 Access
database systems at our company that rely on this to occur correctly,
but it does not. These are databases that have been converted from
Access 97 to Access 2000 format. What we have found is that Access 97
did this 100% correctly, but the newer version of Access is not doing
it correctly 100% of the time. Like I said, its intermittant. It
would be a huge investment of time to recode all these databases to
handle this explicitly. Anyone know of a way to make things work as
they should without recoding?

n00b

 

[BruceM]

"it saves null in all tests I have tried"

What tests are those? Do you mean something that accomplishes the following
by some means or other?

If Len(Me.YourField) = 0 Then
If IsNull(Me.YourField) Then
MsgBox "It's Null"
Else
MsgBox "Something other than Null"
End If
End If

I suppose you could run an Update query in which "" is replaced with Null,
but that would be a lot of work on 50 databases (which seems like a lot of
separate files, but that's another point). For that matter, so would
anything that involves updating that many files.

Since you have not provided much detail I don't know exactly what you have
tried, but the Nz function can be very useful in such cases. If you can
explain just why it matters whether it is Null or an empty string it may be
easier for somebody here to spot the problem or devise a solution.

 

[n00b]

"it saves null in all tests I have tried"

What tests are those? Do you mean something that accomplishes the following
by some means or other?

If Len(Me.YourField) = 0 Then
If IsNull(Me.YourField) Then
MsgBox "It's Null"
Else
MsgBox "Something other than Null"
End If
End If

I suppose you could run an Update query in which "" is replaced with Null,
but that would be a lot of work on 50 databases (which seems like a lot of
separate files, but that's another point). For that matter, so would
anything that involves updating that many files.

Since you have not provided much detail I don't know exactly what you have
tried, but the Nz function can be very useful in such cases. If you can
explain just why it matters whether it is Null or an empty string it may be
easier for somebody here to spot the problem or devise a solution.









- Show quoted text -

Wow we found the answers pretty quick, each of the systems with this
problem (it wasn't all of them) had coding mistakes that were never
detected before. Apparently our testing was very good to have picked
up on these problems. The developers had set textboxes equal to "" to
clear fields thinking that was equivalent to setting the textbox equal
to Null like they should have.

Funny nobody ever noticed that many reports were showing wrong info
becuase the underlying queries were looking for null in the fields.

What we learned is this...

For SQL Server tables with nullable fields and no default values
linked in Access 2003:

1. If the user clears the bound control to one of these fields, null
is stored like it should be. All validations should use the Nz and
IsNull functions appropriately.
2. If you set a control equal to "" in code in a field bound to a
nullable field from a linked SQL Server table, it saves a ZLS in the
field.
3. Access was not bugged, we were LOL.

We were afraid something bad was going to happen, but the problem
wasn't as prevalent as we originally feared and it definitely was not
a bug in Access.

 

[aaron.kempf]

I'm sorry that you're stuck with a huge task load.

Move them all to Access Data Projects.

MDB isn't reliable... you shouldn't get stuck re-writing your queries
every time that MDB decides to take a vacation.

-Aaron