Null string

[Guest]

I'm pulling records from a table to populate another table. I've had to add
a new field which is left null in most of the records. I have the field as a
string. Is there a way to get it to accept the null value and stop the
invalid use of null error message?

 

[Allen Browne]

The field will accept a zero-length string if you open the table in design
view, and set the field's Allow Zero Length property to Yes.

But are you certain this is worth while? You then have 2 cases to consider
in every field where you do this, in every query, and in all validations.
Every query or sort is less efficient as there are 2 cases to consider, and
we won't even start to talk about how you explain the difference to users
who can't see the different between a ZLS and an Null but have to handle a
program that expects them to understand that you are handling these as
different cases.

Would it be easier and more efficient to create a little function that you
can wrap around any value that could be a ZLS, to convert it to a Null, and
handle that at the time the data is being assigned to the field (i.e. once)
instead of *every* time the data is accessed for the rest of the life of the
database?

Function Str2Null(strIn) As Variant
'Purpose: Convert the input to Null if it's a zero-length string.
If strIn = "" Then
Str2Null = Null
Else
Str2Null = strIn
End If
End Function