I have a very simple expression similar to this:

However, even though Me.txtMyText is null, Access is ignoring that and evaluating the IF statement as as FALSE and failing (red line). It says it's trying to use a variable that has no value. Well, DUH, I know that; but Access is chosing to ignore the IsNull() function. This is not the first time this has happened.

I've also tried evaluating for an empty string:

But, again, it fails at the first line because Me.txtMyText has no value at all.

Has anyone experienced this and found a solution to the problem? Thanks.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division

Hi there,

You can try to convert the null value to a Zero length String by appending a ZLS to it and then check for that: Is txtMyText your text box or a variable? Shouldn't you use the Text property? Aren't you checking whether the text box itself is Null now??

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Use the following:

If len(Me.txtMyText & vbNullString) = 0 Then
     MsgBox "The textbox is null.", vbExclamation, "Error!"
Else
     vMsgBox "The textbox says " & Me.txtMyText, vbInformation, "FYI"
End If

Regards,

Beth M

Greg

Another alternative:

If Nz(Me.txtMyText,"")="" Then
      MsgBox "The textbox is null.", vbExclamation, "Error!"
   Else
       MsgBox "The textbox says " & Me.txtMyText, vbInformation, "FYI"
End If


Brian Skelton
Braxis Computer Services Ltd.

I find using ! instead . in the Me.txtMyText expression works better for the property. I leave . only for the method.


Cheers Ray

I tried the NZ() function and it failed, too, because it contained an "object that has no value," i.e. NULL. That is so odd... if it's null, the NZ() and IsNull() functions should catch it but they don't want to function as expected. I did notice that the WHERE restriction when this form opens results in zero records. Even so, those two functions should work.

I wrote a new IF statement basically saying that if the recordset is empty to not even bother to check for nulls and skip that whole section of code. That has eliminated the problem, but the question remains. Why are those two functions failing when the textbox is obviously NULL? They're supposed to test for NULL and are failing because the field is NULL! :(

BTW, I don't use the bang (!) anymore because for coding it's obsolete. When you use periods, Microsoft Access's autocomplete finishes lines of code for you. When you use the bang, it does not autocomplete.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

You can try reversing your logic and take action:

If Not IsNull(Me.txtMyText) THen
    MsgBox "The textbox says " & Me.txtMyText, vbInformation, "FYI"

Have you checked the properties of the text box. I have found some weird scenarios as you describe if the properties are set funny...for example the default value set to ""

Otherwise, I have never heard of this problem and I use isnull quite often

John

I checked. There is no default value specified.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Did you ever find the answer to this? I recently found myself struggling with the exact same issue with a textbox on a report and couldn't figure out why IsNull wasn't working as expected. I finally found out that my textbox had the exact same name as the database field did. So in my case IsNull(SpouseName) was looking by default to the textbox named "SpouseName" and not the actual field "SpouseName" in the database. When I renamed the textbox control to "txtSpouseName", then IsNull(SpouseName) began to work correctly.