Testing field for null in Select Case statement

[Lars Brownies]

I have code something like:

Select case fldMyField
Case "All records":
´other code
Case "This employee:
´other code
Case Null: msgbox "This field can't be empty"
Cancel = True
End Select

The case null statement to test if the field is empty is not working.
if isNull(fldMyField) does work

Why doesn't the case Null statement work? What would be the best way to do
this?

Thanks,

Lars

 

[ken]

Comparison with Null results in Null, neither True nor False, which is
the reason for the VBA IsNull function or IS NULL in SQL. This should
work:

Select Case Nz(fldMyField,"")
Case "All records":
´other code
Case "This employee":
´other code
Case "": MsgBox "This field can't be empty"
Cancel = True
End Select

Ken Sheridan
Stafford, England

 

[Lars Brownies]

Thanks Ken!

<[email protected]> schreef in bericht
Comparison with Null results in Null, neither True nor False, which is
the reason for the VBA IsNull function or IS NULL in SQL. This should
work:

Select Case Nz(fldMyField,"")
Case "All records":
´other code
Case "This employee":
´other code
Case "": MsgBox "This field can't be empty"
Cancel = True
End Select

Ken Sheridan
Stafford, England

 

[ken]

(a) Who said it was? I think you need to read the post more
carefully.

(b) We can't assume that the list of values is exhaustive. There may
well be other legitimate values than those for which the case
construct tests.

Ken Sheridan
Stafford, England