I have three tables for holding the same data types, but one table is "current" list of records, the second table is an "import" table (for new data from an outside source, Excel spreadsheet), and the third table is a "History" table (to remember what the records looked like before they were updated) . I started by building the Current Table, and then copied and renamed it for the other two tables to ensure that the field names and all of their properties were identical. The only change in fields that I made was to the History table because I could not maintain the same key field, so I created an ID field with an autonumber parameter. Also, I have the Allow Zero Length set to no for all fields.
I have an append query that reviews the data in the import table and the current table, looking for any changes. The append query then copies records from the Current table to the History table for any records that change.
When I run the append query, I get several different errors in the final step. The first error I get is "some fields set to null due to type conversion error", and in reality, the records never get appended to the History table. The second error I get is "some records not copied due to validation rule violations".
The challenge is that I imported about 1200 records into the Import table, copied and pasted those same records into the Current table (to fill it the first time), then I went and changed the data in my original Excel spreadsheet that was used for importing, and successfully imported these altered records into the Import table (after deleting all of the records in the Import table from before).
Now when I run the Append query, there are 10 records that error due to the null type conversion thing, and about 17 records that error due to validation rule violations.
I checked all three tables again to be sure that all the fields have the same exact data types and setting, including the one and only field that I have a validation rule on, and they are exact. I even tested the validation rule in each table, and it will not let me import data from my excel spreadsheet if the data violates the rule.
I found my problem. I was using the NZ() function in many of these fields in order to compare Current fields with null vaules to Import fields with real data.
When I looked at the SQL code I noticed that Access carried over the NZ() function for these fields into the SELECT portion of the code, when I only wanted it in the WHERE clause. Once I manually removed all of the NZ() functions from the SELECT clause, the query seems to be working. I will only look at this query in the SQL view because I'm afraid that access will try to help me by altering my SQL if I go to the design view.