Problems with NULL

[Compuboy]

My DB is in Access 2002.
The problem now is I have a field called "CE number" that I want to either have a value in the form of (Input Mask: "CE"######? or null) First of all what exactly do I put into input mask?

However, I've fiddled around with it a lot. My main problem is this...

When "CE number" is empty it responds really funny. Cuz when I check if it is null in an if statement it returns false, but then when I try to MsgBox the "CE Number" it says "Invalid use of null".

Example Code:
If [CE Number] = Null Then
MsgBox "Works"
Else
MsgBox [CE Number]
End If

AND when run, it has a problem w/ MsgBox [CE Number] saying it is an invalid use of null.

ALSO related when there is an actual value typed in for [CE Number]--something it can even msgbox out. It returns false for If [CE Number]<>Null.

In the end all these if statements are just me testing the [CE Number] field. What I want to do is be able to select (in a listbox) the records with matching [CE Numbers]... This selection works with everything unless the [CE Number] field is empty.

ANY Ideas or Thought?? THANKS.

 

[PHV]

Replace this:
If [CE Number] = Null Then
By this:
If IsNull([CE Number]) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Compuboy]

Thanks that helps a lot. but is there a function that is the opposite of IsNull...like Is not null or somethin??

 

[KenReay]

Hi

If Not IsNull() Then

is what you are looking for

but remember that Null is a useful but off "Value" it is not equal to anything including itself so

If MyCol = Null Then

will always return false, no matetr what the value of MyCol (even if it is null!)



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[drctx]

to find not null do this

If Not IsNull([CE Number]) Then