zero length string

[Allison]

Thanks for your responses both Jeff and Duane....

I have a field in a table with the Allow Zero length
string set to yes....The users want to know the difference
between a null and a value that has no entry for a
reason. So I assume that you use a zero length string to
set this condition. So what if a user enters the zero
length string "" in error and wants the field to really be
a null? How does a user change it? thanks..... Allison

 

[Jeff Boyce]

Allison

I'll urge you to consider a different approach. A user, looking at a field
that has nothing in it, will NOT be able to tell the difference between a
Null and a zero-length string -- both look like nothing!

Instead (sorry, this is my former life as a stats instructor speaking), add
a new code/text string/... that indicates "Unknown" or "Undecided" or "No
Entry For a Valid Reason". That way, you and your users can readily tell
the difference between Null (nothing) and "No Entry For ...".

 

[Tim Ferguson]

.The users want to know the difference
between a null and a value that has no entry for a
reason. So I assume that you use a zero length string to
set this condition.

There are wars fought about how to handle nulls...

A practical problem is that an empty text box in Access will return a Null
value rather than "" -- come to that an string of spaces " " will get
trimmed into a null too. Therefore it's rather hard for a user to manage to
enter a "" at all, although you can do it with an

UPDATE Mytable SET Somefield = "" WHERE Something=TRUE

easily enough.

As for display, you can use a controlsource or a query to tell the
difference:

Iif(IsNull(SomeField),"NULL", "<" & Somefield & ">")

or something like that.

Hope that helps


Tim F

 

[RSGinCA]

I agree with Jeff 100 percent. However, if you are working with a form and you
do indeed want to allow the user to change a zero-length string ("") to null,
then you can accomplish that by including a button on the form which executes
the following line of code when pressed:

MyTextField = Null

Rick

 

[Fred]

Another similar approach would be to designate some string that would be
translated by the form into 'null' by the AfterUpdate event of the
field, such as the following code:

Private Sub MyTextField_AfterUpdate()
If MyTextField = "<NULL>" Then
MyTextField = Null
End If
End Sub

When the user typed "<NULL>" (without the quotes) into the field the
text would disappear and the field would be set to null. No need for
any button.