Testing field for null in Select Case statement

Testing field for null in Select Case statement

Post by Lars Brown » Thu, 07 May 2009 04:39:36


I have code something like:

Select case fldMyField
Case "All records":
ther code
Case "This employee:
ther 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
 
 
 

Testing field for null in Select Case statement

Post by ken » Thu, 07 May 2009 05:20:35

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":
ther code
Case "This employee":
ther code
Case "": MsgBox "This field can't be empty"
Cancel = True
End Select

Ken Sheridan
Stafford, England

 
 
 

Testing field for null in Select Case statement

Post by Lars Brown » Thu, 07 May 2009 05:53:48

Thanks Ken!

< XXXX@XXXXX.COM > 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":
ther code
Case "This employee":
ther code
Case "": MsgBox "This field can't be empty"
Cancel = True
End Select

Ken Sheridan
Stafford, England
 
 
 

Testing field for null in Select Case statement

Post by meggie110 » Thu, 07 May 2009 06:39:02

wat is this
 
 
 

Testing field for null in Select Case statement

Post by Linq Adams » Thu, 07 May 2009 06:43:12

A Zero-length string, i.e.

"":

is not the same thing as Null !

Why not just use

Case Else
MsgBox "This field can't be empty"
Cancel = True

Thid includes anything except the stated Cases.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Testing field for null in Select Case statement

Post by ken » Sun, 10 May 2009 01:15:20

(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

On May 5, 10:43 pm, "Linq Adams via AccessMonster.com" <u28780@uwe>