Results 1 to 4 of 4
  1. #1
    deekadelic is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    23

    Paste Operation is Simply Dropping Certain Text Values

    This is similar to, but not the same as, a problem I posted about last month.

    The gist:

    I'm trying to paste data from Excel into an Access table; and there is one field that is causing me major agita. The field holds postal codes, which in the US are all numeric values. However, Canadian postal codes have values like "V1M 3A9" and "L6S 6G6". To accommodate this, I have set the destination field in the Access table with a Short Text data type, which is supposed to contain literal text data, regardless of whether the characters are alpha or numeric.

    The problem is, when I paste the data into Access, the Canadian postal codes simply vanish without a trace, though no error message is given to indicate that values have been dropped. The rest of the data pastes just fine.

    I have included a snapshot of the problem below. On the left is a sample of the data as it appears in the Excel sheet, and on the right is the same data as it appears in the Access table after pasting. Note that the Canadian postal codes are just gone.


    City State Postal Code Consignee City Consignee State Consignee Post Code
    DINUBA CALIFORNIA 93618 DINUBA CALIFORNIA 93618
    WOODLAND CALIFORNIA 95776 WOODLAND CALIFORNIA 95776
    DINUBA CALIFORNIA 93618 DINUBA CALIFORNIA 93618
    Langley British Columbia V1M 3A9 Langley British Columbia
    Brampton Ontario L6S 6G6 Brampton Ontario
    TORRANCE CALIFORNIA 90501 TORRANCE CALIFORNIA 90501
    NICHOLS NEW YORK 13812 NICHOLS NEW YORK 13812






    I cannot for the life of me figure out why this is happening. Can anybody shed some light on this for me?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are you doing a Ctrl V paste, or simple paste or paste special from the ribbon? The data can get dropped when numbers and what looks like text are involved. Some say a Paste Special (ribbon) works if the target field is text. Others say an empty cell above the data solves it, provided the spreadsheet column is formatted as text. One way around the issue (apparently) is to link to the spreadsheet as a table and copy the data using Access queries.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    deekadelic is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    23
    Inserting a blank line above the data does not make a difference. Nor does linking to the spreadsheet as a table; when I do that, Access assigns a Number data type to the field in question, so the Canadian post codes generate #NUM! errors.

    Doing a paste special as text, from the ribbon, did work. However, I'm trying to build this into a streamlined automated process, and that extra step of doing the Paste Special instead of the default Paste operation gets in the way of that.

    I've said this before, but I'll say it again: I cannot believe that Access won't just let me paste in data to a field whose data type accommodates the exact kind of data I'm trying to paste. Why is Access trying to do all this extra work of interpreting the data? I'm telling it the data is text, so why is it overriding me and trying to force the data type to be something else? Why can't Access just treat it as text? This is maddening.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sometimes you gotta wonder who they really had in mind when they made those "Kill Bill" movies!
    If you're doing this in vba, consider the RunCommand method of the DoCmd object (acCmdPasteSpecial).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-10-2016, 01:03 PM
  2. Replies: 3
    Last Post: 05-20-2013, 04:18 PM
  3. Pass form values to SQL JOIN operation?
    By jonman03 in forum Queries
    Replies: 3
    Last Post: 03-06-2012, 04:59 PM
  4. Cut/Paste Feild values to new record
    By nickiwinki in forum Access
    Replies: 14
    Last Post: 10-25-2011, 11:56 AM
  5. Replies: 0
    Last Post: 03-15-2010, 01:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums