Using Select case and isnull

[lcox400w]

I am trying to use case to check for several conditions with an on_exit
event. 2 of the 3 work, the problem won is the Case IsNull. When the code
is called, it just passes it right by even though DRNo is null. Can anyone
tell me what I'm missing to get the isnull to work?

thanks


Select Case DRNo

Case IsNull(DRNo)
MsgBox "dr is null"

Case ""
MsgBox "case is empty"

Case Is < 9
MsgBox "less than 9"

End Select

 

[Douglas J. Steele]

I'd use

If IsNull(DRNo) Then
MsgBox "dr is null"
Else
Select Case DRNo
Case ""
MsgBox "case is empty"
Case Is < 9
MsgBox "less than 9"
End Select
End If

 

[lcox400w]

Excellent thanks!

I'd use

If IsNull(DRNo) Then
MsgBox "dr is null"
Else
Select Case DRNo
Case ""
MsgBox "case is empty"
Case Is < 9
MsgBox "less than 9"
End Select
End If

 

[Dale Fye]

Another thing to consider is the DRNo might be an empty string, rather than
being NULL. Using Dougs example, the easiest way to test for both conditions
is to use something like:

IF LEN(DrNo & "") = 0 then
MsgBox "dr is null or empty"
Else
Select Case DRNo
Case ""
MsgBox "case is empty"
Case Is < 9
MsgBox "less than 9"
End Select
End If

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dennis]

If len(Nz(DrNO)) < 9 then
error
end if

Does everything needed in one statement.

 

[Douglas J. Steele]

I'd considered that, but the original example distinguishes between null and
empty. Using your approach, empty text boxes would get the message "dr is
null or empty" instead of "case is empty". The Case "" will never be
executed, meaning you'd be better off using:

If Len(DrNo & "") = 0 Then
MsgBox "dr is null or empty"
ElseIf DrNo < 9 Then
MsgBox "less than 9"
End If

Of course, it just dawned on me that what the OP probably wants is to
identify those records where DrNo is less than nine digits in length, as
opposed to those records where the value of DrNo is less than 9. That means
it should be

If Len(DrNo & "") = 0 Then
MsgBox "dr is null or empty"
ElseIf Len(DrNo) < 9 Then
MsgBox "less than 9"
End If

or

Select Case Len(DrNo & "")
Case 0
MsgBox "dr is null or empty"
Case < 9
MsgBox "less than 9"
End If