potential bug?: Distinct query and masked field RRS feed

  • Question

  • Access 2010.accdb / Win 10

    In what is a very typical table with names, addresses etc. A distinct query is not returning accurate results. [Unique Values = Yes] 

    It is due to the Zip+4 field which although absolutely identical (sanity checked myself with re-entry multiple times) is still not being considered ‘Distinct’ in the query result. 

    I know this because when I eliminate that field from the query, these duplicates disappear.  The one thing about the Zip+4 field is that it has a mask.

    It is my guess that the mask property is interfering with the Distinct logic inside the query.  But not in every potential duplicate.  There is an intermittent aspect to the missed distincts / duplicates which I cannot determine any pattern.  Or said another way - some duplicates do work ok and result in a distinct record....but others remain in the query result as if they are distinct when they clearly are not.  It is definitely the zip field causing this.

    Is this a known issue?

    The app will survive this, because it is virtually impossible in this data set that 2 records have duplicates of the other fields and not also the zip - so I can eliminate the zip in order to establish distinct - and then with another query join back in the zip field....though it is an extra step of course.....

    Tuesday, November 10, 2015 12:35 AM

All replies

  • Try this test to check field length --

    SELECT ZipPlus,  Len(ZipPlus) AS ZipLen

    FROM YourTable

    ORDER BY Len(ZipPlus);

    Build a little, test a little

    Tuesday, November 10, 2015 2:02 AM
  • Information should be distinct.  The zip field is really 2 pieces of information.  I like to store zip in 2 fields.  This also gives you greater flexibility for querying and reporting.

    To separate, create another field:

    Zip2, text

    then run an update query to move information.  If these are US zips:

    UPDATE [Tablename]
    SET Zip2 = mid([Zip_fieldname],6)
    WHERE len(trim([Zip_fieldname])) > 5

    or, if the mask characters are being stored, use:
    UPDATE [Tablename]
    SET Zip2 = mid([Zip_fieldname],7)
    WHERE len(trim([Zip_fieldname])) > 6

    Once you are happy that the information is moved successfully, change the length of Zip_fieldname to 5 in the table design.

    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Tuesday, November 10, 2015 2:35 AM
  • I understand - your approach is the same as my original in attempting to discover that there must be a difference somehow someway that makes the 2 records distinct.  Queries are never wrong.

    But I manually entered the zip.  I am absolutely certain it was identical. And it still wasn't resulting as distinct - until the zip field was removed from the query.

    I believe somehow someway the mask (implemented as a field property) was responsible for causing a variation that cannot be viewed...... possibly the length check will reveal it and so I'll give it a go but still the user is entering identical data and this is the result - so the problem remains.  I believe the fix is to remove the mask from the table property and move it to the form object ..... but just wanted to check if there was any known issue involving masks and distinct queries.....

    Tuesday, November 10, 2015 1:40 PM
  • Just to be clear, you have only selected the single field? Selecting any other Field including the ID field may cause it to return other than expected results as the ID fields are all unique, depending on your sql setup.

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, November 10, 2015 3:16 PM
  • Is the mask being stored in the field, or only used for formatting?

    Can you prepare a minimal sample database that displays the problem, and post it or e-mail it so we can check it out?

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, November 10, 2015 3:40 PM
  • am very certain the records were duplicate; because as my troubleshooting I literally did a copy/paste field by field of 1 record to the second.....I was eliminating unseen empty character spaces at the end .....

    when these two still passed thru the Distinct query - - then I had no choice but to begin reducing the field count.....and it was the Zip field, when removed, that changed it to be perceived as duplicate....

    the zip field, as a table property, has the mask applied

    the app is fully in service; the distinct record set is established without zip field - and then the zip field is joined back in afterwards in the next query.

    while I am tempted to remove the mask at the table level (I did not write the original app) but this would mean applying it at quite a few form/report points - and so that has not been done... but I tend to think it would alleviate the issue....

    I should repeat - that the issue is intermittent in that there were lots of duplicates that were correctly removed by the Distinct query - - out of ~33k records there were ~45 duplicates getting thru.  I have not been able to detect a pattern of why them and not the others.  Quite a mystery - but the work around is in place.
    Wednesday, November 11, 2015 2:10 AM
  • Hi,

    Based on my understanding, the mask property wouldn't affect the distinct operation. We can remove the mask to verify the result.

    To detect whether the value was caused the same value stored in the database, we can use code below to print the value in the zip filed("field").

    Set rs = CurrentDb.OpenRecordset("select distinct field1 from table1")
    While Not rs.EOF
    Debug.Print rs("Field1").Value

    In addition, here are some helpful links about manipulating Zip codes in Access:
    ACC2000: How to Manipulate ZIP Codes in Microsoft Access

    ACC: How to Manipulate ZIP Codes in Microsoft Access

    If you still have the problem, would you mind sharing a sample database to help us narrow down this issue? You can upload it via OneDrive and please remove the sensitive information before you uploading.

    And if I misunderstood, please feel free to let me know.

    Regards & Fei

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 12, 2015 6:03 AM
  • The mask could have an effect if symbols are being stored. Also, trailing (and leading) spaces will have an effect if the values are not first TRIMmed before comparing, of course.  Have you examined the data to find out why some values are duplicated? Perhaps they are truly different, and just look the same. 

    Add a calculated field:

    LenZip: len([zip_fieldname])

    as suggested by Karl

    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Thursday, November 12, 2015 6:08 AM
  • I cannot say what is the issue with your database. I can say that over the last 2 days I have tried to reproduce the issue you reported and I cannot reproduce the issue in existing nor in new databases when using the mask whether or not I am storing the format in the Table or not.

    Please verify you have the following format on the Table Field

    • Field Size 10
    • Short Text
    • Input Mask 00000\-9999;;_
    • or
    • 00000\-9999;0;_


    2015 11 12  Added 2nd formatting for retaining mask in data

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    • Edited by KCDW Thursday, November 12, 2015 7:41 PM 2015 11 12 Added 2nd format
    Thursday, November 12, 2015 7:37 PM
  • I appreciate everyone's input.  I think we close this as unsolved. Definitely one can't generate the problem from scratch. 

    What I posted previously is all accurate - so I won't repeat it.  This is an app originally built in the ~02 era.  Why would 45 duplicate records out of 30k records total (of which ~500 are duplicate) pass thru the Distinct query - it is a mystery.

    Perhaps there is an element of corruption.  Definitely it was fixed by removal of the masked zip field - and the issue of the mask storing 1 value while displaying another seems relevant.  And yet when I retyped/copied in new values into the zip field for 1 of the 45 dupe sets, it did not clear it - which makes me believe it involves some aspect of the overall record.

    I think we file as a cold case until someone else has a similar experience and we determine some commonality.......

    Friday, November 13, 2015 1:55 PM