Benson, William (GE Global Research, consultant)
Benson at ge.com
Sun May 20 15:57:36 CDT 2012
I pasted some data in a text field in a table, after copying the records from Excel. I could hardly believe my eyes. Access dropped the data (no warning) in the rows where the data had been numeric-looking. It did not drop the records- only the information in the records - for non-numeric data. Field1 Field2 Field3 Formatting: (General) ------------------------------------------------- UnitStat 1 Propose UnitStat 2 Design RatiUnit CC CROSS COMPOUND Resulted in UnitStat 1 Propose UnitStat 2 Design RatiUnit CROSS COMPOUND It didn't seem to matter Access that I had defined the field as TEXT. I repeated this experiment until I came to these observations. With 1 number followed by 1 text, Access recorded both. With 2 numbers followed by 1 text, Access dropped the text (as shown above) If the fields in Excel are numberformatted as Text, then all the copy-pasted data gets transferred to correctly, regardless how many look like text or numbers. Field1 Field2 Field3 Formatting: ( Text) ------------------------------------------------- UnitStat 1 Propose UnitStat 2 Design RatiUnit CC CROSS COMPOUND Resulted in UnitStat 1 Propose UnitStat 2 Design RatiUnit CC CROSS COMPOUND So somehow, numberformatting affects in meta data during the copy operation; and Access pays attention more to that meta data than it does the field type in the table.