I am getting the following MS Access error message:
Microsoft Access set 1 field(s) to Null due to a type conversion failure...
I have a database that was converted from Access97 to Access2000. I am attempting to append information using a standard append query. Some of the fields/columns get their data directly from text and combo boxes. It is those fields/columns that are generating the error. In my append query, I get the information needed using the following statement:
My target tables live on a MS SQL 7.0 server. The target columns are varchar. Both my 97 and 2000 MSAccess versions use the same attatched database/tables. The above statement works correctly in 97 application. To make my statememt work in Access 2000, I have modified it to read:
By the way, I use this method throughout my application and have more than one column that gets it's information from a field. What I want to know is cause and is there a global fix that I can apply so I don't have to go from query to query, column to column to fix this issue.
I am sure that the error message says more than what you wrote. The problem probably is that you are trying to append a "" value to a table field which has it's AllowZeroLength property set to No. The longterm solution is to set your tables' text fields to allow Zero length, (but his does have a downside!) or test the value of the data to be appended with IIF() function . . . . IIF(Not IsNull([Value]),[Value],Null)