Problems with Null

[Sanchez]

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.

 

[Glaanieboy]

Not knowing anything about access, how's this:

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

Note the == in the if.

 

[Sanchez]

I think that's C++, but this problems solved. Thanks anyways.

 

[Glaanieboy]

Tehee, I am good in coding

 

[Zedric]

Tehee, I am good in coding

I don't think he meant that that solved the problem as comparison is = in VB afaik...

 

[Glaanieboy]

Awww, guess I am not that good then. At least I tried

 

[Octopus]

I know I am to late but it's been a while since I posted something here
to get rid of this problem use "iif function" it's not if statement
do it like this

 id.text  = iif(IsNull(DE1.rsDataBase("ID")),empty,DE1.rsDataBase("ID"))

note that:
rsDataBase = is a recordset
DE1 is your data connection
IsNull dont change it this is a syntax
ID is the name of the coloumn in your database

 

[dave holbon]

The correct way to check for nulls is to use the IsNull() function and not (If [CE Number] = Null Then) as this might return a reference to the [field].object and not it’s contents. Of course the contents of the field could also be an empty string or any other value (like a load of spaces so the user can’t see the value or any other unusual value). Taking into account that you have allowed null values to exist in this field and “empty” values (IsEmpty() function will check), it’s no good just checking for null values alone you must check for all values that are illegal or that you do not want as this can produce unexpected errors many months down the development road when you attempt to combine values from ([CE Number]) say in an SQL VB statement (as a string) this will produce errors and depending on the version of Access you are using could produce this exact error. This then becomes very difficult for someone else to de-bug.

Error checking (and hence) stability of operation for any programme is a subject all of its own. I was taught that for every line of code that made up a project three more were required for error trapping and resolution and in those days there were more personnel employed on error trapping and resolution than were actually writing the programme sometimes five times as many.

If you are coercing the string “CE” with say six numbers to a single field in the database that looks to the user like (say) “CE123456” or whatever and storing these values into a single field, then your database design is incorrect. The “CE” part should be stored once and the numbers separately so as to circumvent duplication of the “CE” part. Search the internet for items concerning “Cods theories or normalising forms” or similar. This will remove at source the problem.