Darryl Collins
darryl at whittleconsulting.com.au
Sun Mar 18 17:37:19 CDT 2012
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