NULL vs. EMPTY string

[Jenny Kurniawan]

I have the following code
What I want to do is: to "convert" NULL-Value textboxes to EMPTY string.
Problem: When the textbox has NULL value, I got error message:
Error 94 Invalid use of NULL

I know where the problem is (See below) but I just don't know how to fix it.

TIA - Jenny

Private Sub cmdCancel_Click()
Dim ctl As Control

For Each ctl In Form_Product.Controls
With ctl
If .ControlType = acTextBox Then
If IsNull(ctl.Value) Then
ctl.Value = " " <-- PROBLEM
End If
End If
End With
Next ctl

End Sub

 

[Sandra Daigle]

Hi Jenny,

I can't duplicate your error and I don't see anything wrong in your code -
are you sure that this is the line causing the problem?

You could try this code instead - it just appends an empty string to every
value - which has the same net effect.

For Each ctl In Form_Product.Controls
With ctl
If .ControlType = acTextBox Then
.value=.value & vbnullstring
End If
End With
Next ctl

 

[TC]

Access strips the trailing blanks from values entered in text boxes.

So you might find that your blank strings are changed back to nulls,
regardless!

Also, your code is not using an empty string. It is using a 1-character
string. These are quite different things. For example, you might be able to
store an empty string in a "required" text field, but you will certainly not
be able to store a 1-character blank string in such a field (uless it goes
via a textbox, & Access changes it back to a null!).

What are you really trying to achieve?

TC

 

[Allen Browne]

If these text boxes are bound to fields in a table, this won't be possible.
A Number or Date/Time field cannot contain a space or a zero-length string.
A text field cannot contain a zero length string if the field's properties
(lower pane in table design) have Allow Zero Length set to No.

Can we enquire as to the purpose for doing this? If it's because you are
having trouble with handling nulls, it is important to learn how to detail
with them.

Some quick comments might help:
Nulls: Do I need them?
at:
http://allenbrowne.com/casu-11.html
and
Common Errors with Null
at:
http://allenbrowne.com/casu-11.html