Results 1 to 6 of 6
  1. #1
    kkbharat is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34

    Duplicate entry in unique field in table.

    Hi everyone,

    I have made a table with one of the fields as a unique field. I tried entering the data in this table with the help of a data entry form and surprisingly I was able to make a duplicate entry in the unique field. Please refer to the attached images.



    How did this happen and how to avoid this ? Please help.

    Thanks & regards,

    Bharat
    Attached Thumbnails Attached Thumbnails Image 1.jpg   Image 2.jpg  

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Try a query
    Code:
    SELECT Len(MobileNo) AS [MobileNoLength] FROM tblLeadManagement WHERE ID IN (2122,2143)
    Are both mobile numbers of same length?

  3. #3
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    You are displaying the Index window with your red highlighter. Be sure also the General Tab category: Indexed - is set to Yes (No Duplicates).

    The character count as prior noted - will tell you whether there are unseen characters that make the values be different to a computer though seen the same as a human.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Looks to me like you created a composite index of mobile and id's. If so, the record you show does not violate the index.
    To index only 1 field you use the table property sheet.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    One thing you can try is to delete just the dashes from both of those records (leaving you with a 10-digit "number") and see if it still allows the duplicates. In some character sets, there can be two (or more) characters that look the same in Access, but have different character codes. In Access, chr(45) and chr(150) both display as a dash/hyphen, and I myself have experienced issues because of it. Importing (or cut-and-paste) from other applications can cause this problem.

  6. #6
    kkbharat is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    Hi all,

    Thanks everyone for responding to my query. I tried the select query as suggested by ArviLaanements and got the result as 11 & 10 and now I understand why it accepted seemingly duplicate entries. It must have been input mask I applied to the field and how Access treats the dashes.

    Thanks once again everyone for your suggestions.

    Bharat

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

Similar Threads

  1. Replies: 10
    Last Post: 02-07-2018, 10:00 AM
  2. Replies: 6
    Last Post: 09-10-2017, 05:59 PM
  3. Replies: 4
    Last Post: 12-21-2015, 10:57 PM
  4. Replies: 2
    Last Post: 10-20-2014, 03:09 PM
  5. Replies: 1
    Last Post: 03-04-2013, 08:09 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