Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Unique Index with nulls (Access '97) (Access '97)

    You first have to understand Nulls. It is a common mistake to view Null as having a value of nothing. Therefore, you think that if one record has a null in its indexed fields, it will only duplicate another record having a null in the same field. This isn't the case. Rather than thinking of Null as nothing, think of it as "could be anything". Therefore, 2 records containing identical data and having a null in the same index field will still be accepted.

    Here's something else to consider. How would you evalutate the equation Null=Null? Is this True or False? Try it in debug and see what you get.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    New Lounger
    Join Date
    Feb 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Index with nulls (Access '97)

    Thanks, Mark. That helped a lot. For anyone whose interested in creating a primary key that includes some fields that could be left blank, I did it by simply setting the "Default" property for these fields to an empty string (""). However, note that if someone enters something into one of these fields, and then deletes it, they would get an error message (because that would leave a Null field instead of an empty string, and, of course, Nulls are not allowed in any field that's part of your primary key). This problem can be solved by setting both the "Required" and "Allow Zero Length" properties to "Yes." Access will automatically convert any Nulls to zero-length strings!

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique Index with nulls (Access '97)

    I want to create a primary key on 7 fields, but it's possible for one or more of these 7 fields to be null in any given record. I've therefore resorted to setting up a multi-field unique index on these fields. I've used the Index dialogue box to name the index and define the fields, and I've selected "Unique" and deselected "Ignore Nulls." The index appears to be set up correctly (I've even used the Help feature and gone over it step by step just to be absolutely sure), and yet I can still enter duplicate records and save the table without a problem. What's going on? Note that I created this table by starting with an existing table that had the fields I needed and deleting the existing records.

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

    Re: Unique Index with nulls (Access '97)

    Yes, it will work, but it probably is not a good idea, since you can still get a "duplicate" record where the only difference is that a value is entered in that field. That doesn't seem like a very useful primary OR unique key to me.
    Charlotte

Posting Permissions

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