IsNull failing to work

[mrtn]

Hi

I'm struggling with the logical check in my other code and have created small If statement to check values returned in MsgBox.

The problem is whenever Adviser is blank I get VBA returning the Else part of the code. Also when the IFA's name is entered it does work properly with returning adviser's name.

Any help would be much appreciated.

Private Sub Command238_Click()
 
Dim Adviser As String
 
Adviser = IntroducedBy.Column(1)
 
If IsNull(Adviser) Then
    MsgBox "Please update IFA's name", vbOKOnly, "IFA Details"
Else
    MsgBox "IFA's first name is: " & Adviser, vbOKOnly, "IFA Details"
 
End If
End Sub

 

[John Big Booty]

If the IsNull() function is failing to fire that would suggest that the value being tested may be a Zero Length String (ZLS) rather than Null.

You could amend your code to read;

Private Sub Command238_Click()
 
Dim Adviser As String
 
Adviser = IntroducedBy.Column(1)
 
If IsNull(Adviser) Or Adviser = "" Then
    MsgBox "Please update IFA's name", vbOKOnly, "IFA Details"
Else
    MsgBox "IFA's first name is: " & Adviser, vbOKOnly, "IFA Details"
 
End If
End Sub

If you are in doubt as the the value being held you could insert the following in your code prior to the logical test;

MsgBox "Value being held in Adviser is " & [URL="http://www.techonthenet.com/access/functions/advanced/nz.php"]Nz[/URL](Adviser, "Null")

 

[mrtn]

Cheers John Big Booty! Works perfectly.

 

[Galaxiom]

Another common variant of this test checks for the length concatenated with the ZLS:

If Len(Adviser & "") = 0 Then

BTW: "" can also be written as vbNullString

 

[vbaInet]

And also ...

If Len(Nz(Adviser, "")) = 0 Then