Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Allow null in unique index (A2003)

    I have created a compound index on four fields in a table, and added the Unique property to prevent duplicate values from appearing in the table. Is there a way to modify the index to allow me to omit one or more of the values (ie, the field is not significant for some combinations of the other three fields) and still have uniqueness enforced for the remaining three fields? When I try entering duplicated values of the three fields, Access allows them to be entered. Tried making another index on the three remaining fields, but uniqueness is violated by the records for which the fourth field is significant.

    Thanks
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

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

    Re: Allow null in unique index (A2003)

    Null is not equal to null. Null is a void, a blank, an absence, so it is not equal to anything, not even to another null. So Access does not consider the following records to be equal:

    "John", 37, 12, (null)
    "John", 37, 12, (null)

    One way to get around this is to define a default value for each field, and to treat this default value as 'missing'.
    If you don't like that, you'll have to check your definition of uniqueness in the Before Update event of the form used to enter/edit records; this could become quite complicated.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allow null in unique index (A2003)

    OK - I get it now. Thanks again!
    Looks like I need to define default values in the four lookup tables.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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