Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Michigan, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Preparing for NULL Records (2000)

    Hello everyone. Based on your experience, can someone please explain to me the best way to handle Null fields when working in Access. How do you guys first "set-up" the database/info? How do you prevent against the many Null problems that always seem to pop up on me. Is there some type of function/sample that can go out and convert all Null fields = "??" or something like that? I don't know, I'm just constantly coding using "If...IsNull...Then...field = "??". Well, just looking to brainstorm for ideas here - and thanks for your time.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preparing for NULL Records (2000)

    If I want a field to be filled in, I set its Required property to True.

    Some random remarks about handling Nulls:

    In queries with joins, you can use outer joins to "catch" all records, including those with a Null value in the linking field on one side.

    In expressions in queries, forms, reports and in VBA, you can use the Nz function:

    Nz([Field],0)

    will yield [Field] is it is not null, and 0 is it is null.

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Michigan, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preparing for NULL Records (2000)

    Thanks HansV,

    But what if the field does not HAVE to be filled in (e.g., Home Location #2 or Fax Phone #)? That is, it's not required but the field will hold a Null value. I then want to dump this info to a text file, but am getting an error because a Null value cannot be written into the text file. I understand I can check for the presence of Null values BEFORE writing the file and then just have it write out a blank line. But, I have to check an awful lot of fields first, and was wondering if there were some better way around it. Thanks, Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preparing for NULL Records (2000)

    Strange, but instead of exporting the table to a text file, create a query, where you use Nz([Home Location 2]) etc. and export this to a text file.

    Perhaps others have better ideas...

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Preparing for NULL Records (2000)

    The suggestion from Hans should work, but I'm puzzled about your problem with exporting null fields to text files. We do that on a regular basis using delimited text files and it works just fine. Are you trying to deal with fixed length text files where you have to fill an empty field with spaces or somthing else?
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Preparing for NULL Records (2000)

    You could set the default value to a space (" ") read as quote space quote. or to a 0. Then this will go into each new record unless someone decides something else.
    David Grugeon
    Brisbane Australia

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Preparing for NULL Records (2000)

    That generally isn't a good idea because it's impossible to tell whether a field contains a space or a null just by looking at the data and you would have to filter for either just to be on the safe side.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Aug 2002
    Location
    Michigan, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preparing for NULL Records (2000)

    Thanks everyone. WendellB, the problem is when the user reaches a certain form, they can click a button. The button saves only certain fields from a table into a text file. An API routine is then called to open that text file so the user can immediately read it. If you attempt to write out certain fields from the table that contain a NULL value, then the dump to a text file will error out (I'm using the FileSystemObject component). In this case, prior to writing out the file, I am checking each table field name for an IsNull, Empty, or "" value and then changing it as needed (i.e., "No information recorded"). I have set some defaults as best I can, but you also run into problems there. For example, setting the default phone number to "??" or some other text will "wreck" the phone number input mask. So, I DO have the routine working successfully, but it just uses quite a bit of code to prevent errors when dumping out the text file.

Posting Permissions

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