William Benson
vbacreations at gmail.com
Sun Mar 18 17:43:57 CDT 2012
Not with 10 records into a field defined ad text ...? And with importing you can get warning and a import errors table. Pasting just inserted incomplete data with no alerts I could see if I was pasting into a numeric field but if access is going to just drop the data with no warning then I think you cannot compare these situations On Mar 18, 2012 6:38 PM, "Darryl Collins" <darryl at whittleconsulting.com.au> wrote: > You can have the same issue when importing with the wizard as well. > Access only ready the first top records and guesses / assumes all of the > data is of the same nature. That is why I always manually set them if I am > importing via the wizard. > > Cheers > Darryl > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto: > accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan > Sent: Monday, 19 March 2012 8:06 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Not all values paste from Excel to Access > > 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 > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >