The same values in primary key?

[knov]

Welcome,
I have encountered a problem, I have two records in the database with a duplicate value that should be unique (because its primary key).


Why did this happen? Are there any white marks I can't see?
Is there a query that fixes this problem by adding for duplicate keys e.g. at the end the values _1, _2, _3 etc. ?

thank you in advance

Equipment.mdb

[June7]

Access query doesn't think these values are duplicates. Do a DISTINCT or GROUP BY query. Still returns two records. I can only suggest some sort of corruption.

How did this happen? No idea.

You can type whatever you want as suffix.

[Welshgasman]

They are not dupes. That cannot happen as you set dupes to No?
You have two similar values, but one is 10 chars long, the other 12.

You really should use an autonumber as PK, no matter if you need a visual key for users.

[knov]

thanks for help,
I can't add autonumber to this ms access database, these are client databases.
I am migrating these databases to sqlite format.

yes, they actually differ in length

[ArviLaanemets]

I.e. one of them is "0294150010", and another one is "0294150010 " - as follows they aren't equal, and your Primary Key doesn't have any problems with them!

[knov]

So why
this query: SELECT * FROM CABLE WHERE CODE = "0294150010 " OR CODE = "0294150010" show only one record?

[davegri]

So why
this query: SELECT * FROM CABLE WHERE CODE = "0294150010 " OR CODE = "0294150010" show only one record?

Because only one record matches the criteria.

try this

SELECT *
FROM CABLE
WHERE CABLE.[CODE]="0294150010 " Or len(CABLE.[CODE]) > 10;

to see both.

[ArviLaanemets]

So why...?

Leading characters aren't really spaces (I checked). Last 2 characters of second row Code field have ASCII codes 13 and 10.

[Welshgasman]

Because only one record matches the criteria.

try this

SELECT *
FROM CABLE
WHERE CABLE.[CODE]="0294150010 " Or len(CABLE.[CODE]) > 10;

to see both.

Dave, can you explain that please? In fact if you click into the field, the cursor goes to the end of the visible characters, so I am not sure what the invisible 2 characters might be. I just expected them to be spaces?

@knov
Try
SELECT *FROM CABLE
WHERE (((CABLE.[CODE]) Like "0294150010*"));

Edit:
Never mind, Notepad+ has shown me what is there.

[Micron]

Something like this in query field should help you to find such values. Should be able to use either 10 or 13 as I suspect they'll be there as a pair.

IIf(InStr([rtftext],Chr(13)),"T","F")

Or perhaps don't use rtf field for the data. Methinks it's the only field format that will hold hidden characters and trailing spaces?

[June7]

I cannot get regular text field to hold trailing Cr or Lf by typing Ctrl+Enter or copy/paste but I can with an SQL INSERT (not UPDATE).

OP's field is a normal text, not MEMO (Long Text) set to RTF. So I presume some SQL INSERT was involved in causing this issue.

[Micron]

Ahh, ok; I confess to not uploading and looking at the sample.