Problems with Null

Discussion in 'Web Design & Coding' started by Sanchez, Aug 3, 2004.

  1. Sanchez

    Sanchez OSNN Junior Addict

    Messages:
    14
    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.
     
  2. Glaanieboy

    Glaanieboy Moderator

    Messages:
    2,626
    Location:
    The Netherlands
    Not knowing anything about access, how's this:
    Note the == in the if.
     
  3. Sanchez

    Sanchez OSNN Junior Addict

    Messages:
    14
    I think that's C++, but this problems solved. Thanks anyways.
     
  4. Glaanieboy

    Glaanieboy Moderator

    Messages:
    2,626
    Location:
    The Netherlands
    Tehee, I am good in coding :D
     
  5. Zedric

    Zedric NTFS Guru Folding Team

    Messages:
    4,006
    Location:
    Sweden
    I don't think he meant that that solved the problem as comparison is = in VB afaik... ;)
     
  6. Glaanieboy

    Glaanieboy Moderator

    Messages:
    2,626
    Location:
    The Netherlands
    Awww, guess I am not that good then. :( At least I tried :D
     
  7. Octopus

    Octopus Moderator

    Messages:
    1,200
    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
    Code:
     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
     
  8. dave holbon

    dave holbon Moderator

    Messages:
    1,014
    Location:
    London England
    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.
    :) :)