Results 1 to 8 of 8
  1. #1
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85

    Exception to the unique rule?

    I have an index in one of my tables so that a certain combination of fields in the table can never be identical. But is it possible that an exception can be made based on a particular value that is chosen for one of the fields so that the record can be duplicated?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I sincerely doubt it.?
    You want to have your cake AND eat it.

    You would need to check manually yourself, and allow that one, likely to become more, exception.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with gasman -- you would have to develop some custom logic to mandate uniqueness with 1 exception.
    You might want to describe the issue in more detail -perhaps an example or 2 - so readers can see your "issue/opportunity" in context.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    One possibility
    - add a 3rd field to the index (I doubt it matters if you set "ignore null" to yes or no)
    - pad 3rd field with zls (zero length strings) for all records except the ones you want to have dupes in.
    This would mean that your data entry would have to insert zls in all records of 3rd field except for the odd time you want to repeat a combination.
    Might be better to remove the index and control data input through your form by looking for the proposed combination via DLookup and if found, don't allow form save. Or do you, sometimes? that's the fuzzy part.
    Last edited by Micron; 06-20-2022 at 09:47 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    Thank you all for the advice.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome. Hope it made sense, but if not, I likely won't know that for over a week - until I get off the cruise ship and back on the road home!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Micron View Post
    You're welcome. Hope it made sense, but if not, I likely won't know that for over a week - until I get off the cruise ship and back on the road home!
    Come on. They must have the Internet on cruise ships these days? roflmao
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Ja, but I'm not only too cheap to buy painfully slow wifi, I figure, why should I work for free when I'm on vacation??
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Certain data exception
    By azhar2006 in forum Queries
    Replies: 2
    Last Post: 03-18-2016, 10:39 AM
  2. Null or unique value - table data validation rule
    By jaworski_m in forum Database Design
    Replies: 3
    Last Post: 07-20-2015, 03:26 AM
  3. Replies: 2
    Last Post: 12-27-2013, 07:32 AM
  4. Exception in unique field
    By neo651 in forum Access
    Replies: 2
    Last Post: 07-01-2011, 02:23 PM
  5. Exception Reporting
    By shexe in forum Queries
    Replies: 16
    Last Post: 09-09-2010, 09:14 AM

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