Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    7

    Unanswered: Blank Numeric/DateTime Fields

    I am relatively new to SQL and have come upon a problem. I work in a Clinical Studies department. It is important to my supervisors that no fields are left blank. This becomes a problem for numeric and date fields where the data is unknown. I do not want to use such concentions as 9999 or 01/01/1901 for unknowns due to the fact that they have real value. Essentially I need a code convention for unknown numeric and date values that denotes the data therein as unknown, as opposed to ridiculously large or small.

    Does anyone have any suggestions?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use NULL

    that's what it is intended for -- to indicate unknown values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    7
    Thanks. That's what I wanted to use from the start, however she views the database from an Access adp so she sees blank fields and that bothers her. Oh well, seems like I don't have any other option. Thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use IIF(ISNULL(field),'this field is null',field) in order to show something when there's really nothing there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Posts
    7
    I can't use that in a numeric field or date/time though, correct?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    uh..., er, i mean, um..
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2005
    Posts
    7
    haha, thanks though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •