Null vs. Empty String

[Mark Koopman]

Is there a quick way to make all textboxes on the screen
equal to null if they are empty? Sometimes they seem to
equal "" instead of Null, and when I try to save the data
to the tables (using ADO), it always says that it can't
save because the field does not allow zero-length
strings. What is the quickest way to check all textboxes
(that are empty) equal Null? Should I loop through the
controls (If TypeOf control Is TextBox And control = ""
Then control = Null), or is there a more efficient way to
do this? Thanks in advance for any suggestions.

Mark Koopman

 

[Bruce M. Thompson]

Is there a quick way to make all textboxes on the screen

equal to null if they are empty? Sometimes they seem to
equal "" instead of Null, and when I try to save the data
to the tables (using ADO), it always says that it can't
save because the field does not allow zero-length
strings. What is the quickest way to check all textboxes
(that are empty) equal Null? Should I loop through the
controls (If TypeOf control Is TextBox And control = ""
Then control = Null), or is there a more efficient way to
do this? Thanks in advance for any suggestions.

To check:

If Len(Trim([Me.MyControlName.Value) & vbNullString) > 0 Then
'The Control contains a value
End If

I presume that you are opening a recordset and assigning the values in the
form's controls to the fields in the recordset, so only assign the value of each
control if the above test evaluates to true. Let me know if this is not how you
are doing things.

 

[Albert D. Kallal]

You are correct, and having to deal with both "" and null can be pain

Often, the text box can have all 3 states:

not null
""
null

The above usually happens when you have a un-bound text box with a default
value. Another case is when you add a new field to EXISTING database. If you
latter change the default of the field at the table level, or even add a new
field, then all of the values are null. Use caution when you change the
default, since as a result you can get a mix of fields in your database with
both zero length and null. To be blunt, for all text fields I do NOT allow
zero length fields.

If you are using un-bound text boxes, then you have to use caution, as again
if you have a default set, you can often wind up with text boxes that have
all 3 states. Either bind the text box to a field, or as the other poster
mentioned, use a len check with nz....