• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

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

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.
  • 2
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
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.
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!!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now