Results 1 to 3 of 3
  1. #1
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    Composite Index With Null Field Not Working

    Apparently I didn't understand the behaviour of composite indexes that contain null fields when I thought I did. I was surprised to learn by accident that my table design allows this and I tried every index configuration I can think of to get what I want. This is more of an information thing for those who were or might be mislead by the Access system help when choosing Yes or No for the "Ignore Nulls" setting on a composite index.

    Let's say I have 4 fields of which I desire to allow null in only the last one and the constraints need to be:
    - the first 3 are required
    - none of these 3 are part of a PK index


    - the first 3 cannot be identical if the 4th is null or empty string
    - the first 3 can be identical if the 4th is not null or empty string
    - the 4 taken together must be unique

    If I have an index named SlideNo comprised of A2, B2, C2, D2 with properties Primary > No; Unique > Yes; Ignore Nulls > No then
    (keep in mind that the description for nulls settin states "If YES records with a null value are excluded from the index").
    The inference then, is that if set to NO (as in NO, DON'T IGNORE NULLS), records with a null value will be included in the index. Thus in the sample below, the last record should not be allowed, but this is what I can do with these settings:

    A2 B2 C2 D2
    1 1 1
    1 1 1 1
    1 1 2
    1 1 3
    1 1 3

    Click image for larger version. 

Name:	IndexProps.jpg 
Views:	16 
Size:	18.0 KB 
ID:	37620

    There are those who would post that Null cannot be compared to anything, and I get that. I don't see a comparison being made here (at least not by me). What I had was an expectation that Access would not ignore nulls in my index, thus not allow the last sample record. After hours of fiddling, there is a solution.

    Apparently, I have to first
    - remove my index and set 4th field to allow empty strings ("") Forgetting that property will cause your update query to fail.
    - then run an update query to change all 4th field nulls to ""
    - then ONLY ever populate AND update this table with a query that attempts to set the 4th field to "" when a value isn't provided on the form.

    While entering/editing data in tables is generally ill advised, I doubt there's any developer anywhere who has never done so. In this case, it's downright dangerous.
    I also recall posters saying "I never allow empty strings" or some such thing. Hopefully you don't have any composite indexes where nulls are allowed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Index with Null values can not be unique! Set Unique to No!

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try what you suggest. You'll see it makes no difference.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Index or Primary Key Cannot Contain a Null Value
    By mrmmickle1 in forum Access
    Replies: 19
    Last Post: 11-12-2014, 06:20 PM
  2. Replies: 4
    Last Post: 01-14-2013, 11:50 PM
  3. Replies: 7
    Last Post: 04-23-2012, 01:25 PM
  4. Replies: 3
    Last Post: 10-11-2011, 01:43 PM
  5. index/primary cannot contain null value
    By BinHDreW in forum Access
    Replies: 1
    Last Post: 05-26-2011, 08:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums