Problems with Null


OSNN Junior Addict
8 Jun 2004
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"
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.
Not knowing anything about access, how's this:
If [CE Number] == Null Then
MsgBox "Works"
MsgBox [CE Number]
End If

Note the == in the if.
I think that's C++, but this problems solved. Thanks anyways.
Glaanieboy said:
Tehee, I am good in coding :D
I don't think he meant that that solved the problem as comparison is = in VB afaik... ;)
I know I am to late but it's been a while since I posted something here :p
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
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.
:) :)

Members online

No members online now.

Latest profile posts

Also Hi EP and people. I found this place again while looking through a oooollllllldddd backup. I have filled over 10TB and was looking at my collection of antiques. Any bids on the 500Mhz Win 95 fix?
Any of the SP crew still out there?
Xie wrote on Electronic Punk's profile.
Impressed you have kept this alive this long EP! So many sites have come and gone. :(

Just did some crude math and I apparently joined almost 18yrs ago, how is that possible???
hello peeps... is been some time since i last came here.
Electronic Punk wrote on Sazar's profile.
Rest in peace my friend, been trying to find you and finally did in the worst way imaginable.

Forum statistics

Latest member