William Benson
vbacreations at gmail.com
Sun Mar 18 17:04:14 CDT 2012
Bug bug bug! Nyah. But thanks for a logical answer Stuart. On Mar 18, 2012 5:06 PM, "Stuart McLachlan" <stuart at lexacorp.com.pg> wrote: > It's not a "bug" in Access, just the way that things work if you are using > Excel Copy and > Access Paste and the Clipboard. > > When working within an application, the Clipboard can use "Registered > Formats" and > "Private Formats" which lets Excel store metadata about the values. > > When working between applications, the Clipboard is basically limited to > "Standard Formats" > so Access can't get that metadata and has to examine the actual text on > the Clipboard and > make assumptions about it. > > Apart from a couple of odd formats, the clipboard is basically limited to > holding images and > chunks of text and Access can only use what Excel puts there. > > It appears that Access "guesses" the format based on the first ten rows > (which in your case is > a header and 9 numeric values) the same way that it does with Import from > a text file. > > -- > Stuart > > On 18 Mar 2012 at 20:15, Benson, William (GE Global Re wrote: > > > I want to say from the start, I worked around this problem by changing > my Excel fields to text number format, but here goes, I still think this is > a bug in Access. > > > > I set up a table called Equipment Model with 3 fields. The first was an > autonumber, the next two were text. In Excel I had a table like this: > > > > Blank Field Equipment Code Equipment Model > > CIGS 85 > > CIGS 90 > > CIGS 130 > > CIGS 135 > > CIGS 140 > > CIGS 145 > > CIGS 150 > > CIGS 155 > > CIGS 160 > > Controller SunIQ > > Inverter 700kW > > Inverter 714.2kW > > Inverter 1MW > > Skid (STP) Transformer > > > > I copied the three columns from Excel and pasted into the Access table. > Only the items up to and including the Equipment Model = 160 record got > pasted. > > > > If it was a numerical field, I could understand it, but it is a text > field. And it is a really small number of records, so I can hardly picture > Access getting confused. > > > > So I went back to Excel and checked NumberFormat - it was General. (At > this point I am thinking "So what, there are some text entries and besides, > the field is set up as text in Access"). > > > > Anyway, I changed number format in Excel to Text, and retried. They all > pasted that time. > > > > I put this down as learning to deal with Microsoft's craziness, but I am > happy for someone to point out any improper expectation on my part as a > user/developer! > > > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >