Difference between IsNull and ""

[gheyman]

I am assuming there is a difference between IsNull and setting the value of a Textbox to ""

I have an If formula that uses a If Isnull Text97.... that works perfectly when the form is just opened and nothing is done to Text97.

Then I have some functions that reset Text boxes to "" (Example: Text97 = "")

I noticed when I reset the values to "" the IF Isnull no longer functions - I assume because the Textbox is no longer Null it is "".

Can you make/force a Textbox to be Null?

 

[xenou]

Instead of resetting to "" you can reset to null:

Text97 = Null

Textboxes should generally be null if nothing was done with the value and there is no default (i.e., such as in a blank form).
You can always check for both:
If Nz(Me.myTextbox.value) = "" Then ...

 

[gheyman]

Thanks

 

[xenou]

For what it's worth I have found that textboxes that have had values deleted from them (by a user - in the form UI) don't get a null value after the deletion, but a blank string. So I never trust the contents of "blank" textboxes or comboboxes - I always check for null and for empty string. Null in case it starts out that way, and empty string in case it gets to be that way.

 

[gheyman]

Good to know. I am sure that will be useful as I build my database. I didn't realize deleted values would not be the same as null.

Thanks!!

 

[HiTechCoach]

I have found that textboxes that have had values deleted from them (by a user - in the form UI) don't get a null value after the deletion, but a blank string.

That depends ... that it is only true in some cases, but not always.

Bound vs Unbound Text Box:

There is a difference between bound and and bound text box.

A textbox bound to a field that allows Nulls but NOT Zero Length (no "") then it WILL have a null value if the user blanks out / empties the control.

Field Property: Allow Zero Length

If this is set to NO then a field can't be empty/zero length/ or = "". It can be Null.

I normallyalways set my text fields to [Allow Zero Length] = No.


How to test for both Null and Zero Length/Empty String ( "" )

If IsNull(Me.Textbox) Or Me.Textbox = "" Then

OR

If Len(Me.Textbox & vbNullString) = 0 Then

OR

If Nz(Me.Textbox.Value,"") = "" Then

 

[xenou]

Ah, I should have said not that they get an empty string but that they *sometimes* get an empty string ... good correction.

 

[HiTechCoach]

For bound controls on a form, it also depends on if the record is dirty (changed but no saved) or not.

That is why I find it best to check the len() using one of the following:

If Len(Me.Textbox & vbNullString) = 0 Then

OR

If Nz(Me.Textbox.Value,"") = "" Then