Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003

    Question Unanswered: Error with append query

    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:

    SectionID: [Forms]![frmDBLogin]![fldAuthorizedSectionID]

    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:

    SectionID: Trim([Forms]![frmDBLogin]![fldAuthorizedSectionID])

    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.

  2. #2
    Join Date
    Jul 2004
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts