On my append query getting error messages, "Type conversion failure"," property value too large"

Posted on 2012-08-21
Last Modified: 2012-08-27
I have a query that when I go to append I get an error message,"Type conversion failure ". Then when I hit to preceed any way I get another error message saying, "property value too large",

Now the tables are the same all I did was copy and paste the table and rename it to make the table that it appends to.
Question by:gigifarrow
    LVL 65

    Expert Comment

    by:Jim Horn
    A 'Type Conversion Failure' essencially means you tried to insert an bowling ball into a garden hose, or in the case of Access, a string (with values in non-date format) into an Date/Time column, an Long Integer into an Integer column, and Integer into a Byte column, etc.

    Check the data types of all columns you're selecting vs. what you're inserting into.
    LVL 11

    Accepted Solution

    First ... I am compelled to tell you that you table is designed horribly :-/  !!!  You are treating a Table as if it were a spreadsheet, and they are two completely different animals!  I would suggest that you read up a little bit on proper table design for a relational data model.

    Terms to search for are "Database Normalization".  To start you off, here is one such article from Microsoft, but there is tons of information on EE, UtterAccess, and a variety of other sources regarding the topic of proper table design.


    Now, for your issue at hand.  The following fields are defined as being Required in both tables ...


    However, the source table does not contain valid information in those fields, in other words those fields are set to Null in all your records.

    With the source table containing Nulls in those fields, the source is trying to write Nulls to the destination, but since those fields are Required in the destination, you get an error raised and cannot insert the records.

    The way you get a Null value in a Required field is by changing the Required property  AFTER data is written to a table, then upon saving that new field definition, you are prompted by Access as to whether or not you wish to evaluate existing records based on the new validation requirement.  If you answer "No" then existing records that violate the validation are allows to remain in the table --- that is, of course, until you have to edit the data in those records in violation of the new rule.

    Just as an FYI: I used A2010/64bit to troubleshoot your db.
    LVL 11

    Expert Comment

    Thanks for the recognition of the answer!

    I know the thread has aged a little, but as I was reviewing, I noticed that I forgot the link to a normalization description by MS, so here it is!!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now