Textbox null problem

Question

I have a textbox and a button on my Access form. In the click event of the button i want to see if the textbox is empty, if it is, nothing will be executed. So i use

If Me.textbox.Value = Null Then
    Exit Sub
End if

But it doesn't work... I checked the textbox.value in the execution window and it is Null, but the if clause just doesn't work... Why?

EDIT: @Dimse, I tried "", doesn't work. And also textbox.text = Null, it pops an error telling me the textbox is not active.. Very strange.

Calamanco

You don't need the .Value property in this case, as it's the default property of controls. If IsNull(Me!textbox) Then should suffice (or, if you've bound the control to a field that allows the evil zero-length string, you would need to test the length of the control's value concatenated with an empty string, If Len(Me!textbox & vbNullString) = 0 Then -- but I'm just repeating what everybody else has said, absent the explicit .Value property. – Acanthaceous

Answer 1

Null is never equal to anything, not even Null. Use the IsNull() function.

If IsNull(Me.textbox.Value) Then

If you want Me.textbox treated the same when it contains an empty string as when it's Null, concatenate an empty string to it and check the length of the combined string:

If Len(Me.textbox.Value & "") = 0 Then

You could also use the named constant, vbNullString, instead of the string literal, "", for an empty string.

If Len(Me.textbox.Value & vbNullString) = 0 Then

Using the string literal requires VBA to construct that string from scratch each time. With the named constant, VBA only needs to reference it, so should be faster and use less memory. However in many (probably most) cases, the performance advantage with vbNullString would be so minor that you wouldn't notice the difference. Also see the comment below from David-W-Fenton.

For me, the more compelling reason to use vbNullString is that it's instantly recognizable to my aging eyes. Conversely, with the string literal, it takes (a tiny bit) longer for me to confirm that "" is not actually something else ... like " " or "'". The only downside with vbNullString, IMO, is that requires more typing than "".

And finally, although you don't actually need to explicitly reference the Value property (since it's the default property of a text box), I left it in because you had it that way and because I prefer to be explicit with Value, too. :-)

Courtneycourtrai

In VBA code, you should get in the habit of using the named constant vbNullString instead of an empty string, i.e., "", because the memory for the named constant is already allocated. This doesn't matter in most cases, but in a loop it might, so getting in the habit insures you'll get it right when it does matter. – Acanthaceous

That vbNullString tip, very useful. – Algie

Answer 2

I also apologize for being awaking the dead, but i'm wondering that no one has considered the use of Nz Function (see it @MSDN), very popular in VBA, also usable in Access/SQL and in my opinion the more convenient, concise and powerful solution for nullable values in expressions.

Swoosh

Yes, Nz is the easiest way to handle null in controls. – Otho

Answer 3

I apologize if I am awaking the dead, but just for completeness sake I am going to give the code for how to test for spaces (visibly 'blank/empty') as well:

If IsNull(Me.Textbox) Or Trim(Me.Textbox) = vbNullString Then
If Trim(Me.Textbox & vbNullString) = vbNullString Then 'Shorter version
If Len(Trim(Me.Textbox) & vbNullString) = 0 Then 'Shortest version

I came here looking for how to handle spaces, empty/ZLS, & NULL's

Microvolt

Answer 4

Expand your sub like so:

If is null(Me.textbox.Value) Or (Me.textbox.Value = "") Then
    Exit Sub
End if

Deposition

Answer 5

Null is not equal to another Null ;)

try If isNull(Me.textbox.Value) Then

Abirritant

Ahhh I didn't see your answer.. Thanks anyway – Calamanco

Answer 6

I think you may need to check againt "", the empty string, and not Null.

Circumflex

Answer 7

I couldn't get this to work, since I was using the KeyUP event. So instead, this is what worked for me.

If(Textbox.Text = '') 
  ...

Since Textbox.Value only updates on change event, it wasn't updating on keyup, so Textbox.Text is what is currently in the box.

Summary: Alternatively, Use the .Text property

Conduct

Answer 8

Just use a second criteria, that will work !!
In this case just a simple word like "check".

If Forms![Basic]![Table.Item] & "check" = "check" Then

MsgBox "Field Empty"

Else

MsgBox "Field Not Empty"

End If

Prefabricate

But does this probe for null values? – Algie

Answer 9

I saw this somewhere and thought I'd share:

If Len(Trim(Me.txtBox.Value)) > 0 Then

Templia