Duplicates and wrong formats in my table!! (1 Viewer)

Ipem · Sep 25, 2013

I have encountered a very strange phenomena, which shocked my confidence in Access, and reminds me very mutch to excel!

I have a table with an indexed (No duplicates) text field as primary key. The values are actually numbers, but I have chosen the text format to keep leading zeros. Today I have discovered, that there are some items in there that Access can't find (with any of its find methods), and also some duplicates made their way in, probably, because of the same reason.

The table is handled trough VBA code like:

Code:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("MyTable", dbOpenDynaset, dbDenyRead)
rst.Edit 'or rst.AddNew
rst![Field]=something
rst.Update

Investigating the issue, I have copied the table, and pasted it into excel. Surprisingly, some entries were texts, but other are numbers! Sorting and filtering did not work in excel either, unless I have converted them all to the same format.
In Acces table, and in forms also these "wrong" records looks like all the others, except they can not be found.

What have happened?

Thanks,

Edit:
When I select the column, and paste it into notepad, I get a pattern like this:
4944370
4950137
"4950139"
4950141
"4950563"
4950597
4955602

pr2-eugin · Sep 25, 2013

Is it possible to upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

Ipem · Sep 25, 2013

Thanks pr2-eugin, for the suggestion. It would take a while to strip my db, since it is splitted. I will certanly do it, if I won't be able to solve it elsehow soon.
I had some other things to do, but in the afternoon (GMT+1) I did some investigation.
The wrong records in the table are actually texts, but with some invisible characters attached. This explains why the find methods don't find these, and also explains the apparent duplications.

I have identified the origin of the wrong values! They are coming from an unbound text box. This text box is filled normally with a barcode reader, or sometimes excel columns are pasted there. I have solved the unique handling of the values with split and join functions.
If the table is filled by pasting an excel column, the invisibly wrong values are born!
Here is the code for the data entry:

Code:

Dim dbs As DAO.Database
Dim recset As DAO.Recordset
Dim i As Integer, problem As Integer
Dim rows() As String, jo As String, list() As String
    
rows = Split(JOBbox.Value, vbLf) 'JOBbox is the unbound textbox
For i = 0 To UBound(rows)
    list = Split(Join(list, Chr(1)) & Chr(1) & Join(Split(rows(i)), Chr(1)), Chr(1))
Next
Set dbs = CurrentDb
Set recset = dbs.OpenRecordset("In_Production")
For i = 1 To UBound(list)
   If CDbl(list(i)) > 999999 And CDbl(list(i)) < 10000000 And DCount("[JOB]", "In_production", "JOB = '" & list(i) & "'") = 0 Then
        recset.AddNew
        recset!JOB = list(i)
        recset![Shop Pack] = Now
        recset.Update
    Else
        problem = problem + 1
    End If
Next

The code handles spaces and vbLf as delimiters

What could be that invisible character, which can sneak in from excel clipboard, looking excately the same, but messing things up? I have tried to look it in message box, Debug.print, and locals window. The only sign is that in the locals window appearently there is a space after the number like "1234567 ", but if I select it, that space disappears.

Any ideas?

Ipem · Sep 25, 2013

I have solved the problem by insetring this:

Code:

For i = 1 To UBound(list)
        list(i) = CStr(CDbl(list(i)))
Next

It was trivial once I realised, that the ghost character went trough the cDbl validation without type mismatch, so cDbl awares of this character.

I won't flag this thread "Solved" untill tomorrow, maybe somebody knows what was this, so I can be aware too.

Galaxiom · Sep 26, 2013

Ipem said:
The values are actually numbers, but I have chosen the text format to keep leading zeros.

I noticed that all your example values had seven digits. If this is the case for all the values then I would store them as Long Integers. They can be displayed with leading zeros using the Format property of the controls on forms and reports.

Long is far more efficient to store and sort than text.

Ipem · Sep 26, 2013

Thank you all for the help, I learn here something every day.

Duplicates and wrong formats in my table!! (1 Viewer)

Ipem

Registered User.

pr2-eugin

Super Moderator

Ipem

Registered User.

Ipem

Registered User.

Galaxiom

Super Moderator

Ipem

Registered User.

Similar threads

Users who are viewing this thread