[AccessD] Pasting Mixed Type into Text Fields failing

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.




More information about the AccessD mailing list