ODBC Query Problem

Question

I Inherited a Microsoft Access database that was written over 19 years ago. I did not write it, and the person who did is no longer available. It has been working fine till recently.
I have looked at the source, and think it is unnecessarily complex. The Access Front End is linked to tables in a SQL Server Back End. There is a append Query that has all the sudden started to issue error messages. The contents of the append Query appear below.

INSERT INTO DATA_CUST_QUOTE_LINE_TBL_1 ( PREV_QUOTED, NEW_QUOTED, PREV_EXTENDED_PRICE, NEW_EXTENDED_PRICE, ORDER_DATE, CUSTOMER_ID, QUOTE_DATE, WON_LOSS_DATE, QUOTE_ID, LINE_NO, PART_ID, CUSTOMER_PART_ID, DRAWING_ID, DRAWING_REV_NO, ORDER_QTY, UNIT_PRICE, EXTENDED_PRICE, CUSTOMER_NAME, PRODUCT_CODE, COMMODITY_CODE, CUST_GROUP, SALESREP_ID, TERRITORY, SIC_CODE, STATUS, CURRENCY_ID, TYPE, QUOTE_LINK_TYPE, PART_CREATE_DATE, MASTER_QUOTE_ID, MASTER_QUOTE_LINE, MASTER_USER7 )
SELECT IIf([vCUST_QUOTE_LINE]![QUOTE_DATE]<[Forms]![New Part Performance]![FromDate],1,0) AS PREV_QUOTED, IIf([vCUST_QUOTE_LINE]![QUOTE_DATE] Between [Forms]![New Part Performance]![FromDate] And [Forms]![New Part Performance]![ToDate],1,0) AS NEW_QUOTED, IIf([vCUST_QUOTE_LINE]![QUOTE_DATE]<[Forms]![New Part Performance]![FromDate],[vCUST_QUOTE_LINE]![EXTENDED_PRICE],0) AS PREV_EXTENDED, IIf([vCUST_QUOTE_LINE]![QUOTE_DATE] Between [Forms]![New Part Performance]![FromDate] And [Forms]![New Part Performance]![ToDate],[vCUST_QUOTE_LINE]![EXTENDED_PRICE],0) AS NEW_EXTENDED, vCUST_QUOTE_LINE.ORDER_DATE, vCUST_QUOTE_LINE.CUSTOMER_ID, vCUST_QUOTE_LINE.QUOTE_DATE, vCUST_QUOTE_LINE.WON_LOSS_DATE, vCUST_QUOTE_LINE.QUOTE_ID, vCUST_QUOTE_LINE.LINE_NO, IIf(vPARTS!PART_ID Is Null,vCUST_QUOTE_LINE!PART_ID,vPARTS!PART_ID) AS PART_ID1, IIf(vPARTS!CUSTOMER_PART_ID Is Null,vCUST_QUOTE_LINE!CUSTOMER_PART_ID,vPARTS!CUST OMER_PART_ID) AS CUSTOMER_PART_ID1, IIf(vPARTS!DRAWING_ID Is Null,vCUST_QUOTE_LINE!DRAWING_ID,vPARTS!DRAWING_ID ) AS DRAWING_ID1, IIf(vPARTS!DRAWING_REV_NO Is Null,vCUST_QUOTE_LINE!DRAWING_REV_NO,vPARTS!DRAWIN G_REV_NO) AS DRAWING_REV_NO1, vCUST_QUOTE_LINE.ORDER_QTY, vCUST_QUOTE_LINE.UNIT_PRICE, vCUST_QUOTE_LINE.EXTENDED_PRICE, IIf(vPARTS!CUSTOMER_NAME Is Null,vCUST_QUOTE_LINE!CUSTOMER_NAME,vPARTS!CUSTOME R_NAME) AS CUSTOMER_NAME1, IIf(vPARTS!PRODUCT_CODE Is Null,vCUST_QUOTE_LINE!PRODUCT_CODE,vPARTS!PRODUCT_ CODE) AS PRODUCT_CODE1, IIf(vPARTS!COMMODITY_CODE Is Null,vCUST_QUOTE_LINE!COMMODITY_CODE,vPARTS!COMMOD ITY_CODE) AS COMMODITY_CODE1, IIf(vPARTS!CUSTOMER_GROUP Is Null,vCUST_QUOTE_LINE!CUST_GROUP,vPARTS!CUSTOMER_G ROUP) AS CUSTOMER_GROUP1, vCUST_QUOTE_LINE.SALESREP_ID, vCUST_QUOTE_LINE.TERRITORY, IIf(vPARTS!CUSTOMER_SIC_CODE Is Null,vCUST_QUOTE_LINE!SIC_CODE,vPARTS!CUSTOMER_SIC _CODE) AS CUSTOMER_SIC_CODE1, vCUST_QUOTE_LINE.STATUS, vCUST_QUOTE_LINE.CURRENCY_ID, 'Q' AS TYPE, IIf(vPARTS!PART_ID Is Null,Null,'M') AS QUOTE_LINK_TYPE, vPARTS.CREATE_DATE, vPARTS.QUOTE_ID, vPARTS.QUOTE_LINE, vPARTS.USER_7
FROM vCUST_QUOTE_LINE LEFT JOIN vPARTS ON (vCUST_QUOTE_LINE.CUSTOMER_PART_ID = vPARTS.CUSTOMER_PART_ID) AND (vCUST_QUOTE_LINE.LINE_NO = vPARTS.QUOTE_LINE) AND (vCUST_QUOTE_LINE.QUOTE_ID = vPARTS.QUOTE_ID)
WHERE (((vCUST_QUOTE_LINE.CUSTOMER_ID) Between [Forms]![New Part Performance]![CFrom] And [Forms]![New Part Performance]![CTo]) AND ((vCUST_QUOTE_LINE.QUOTE_DATE) Between [Forms]![New Part Performance]![txtFromDate] And [Forms]![New Part Performance]![txtToDate])) OR (((vCUST_QUOTE_LINE.ORDER_DATE) Between [Forms]![New Part Performance]![txtFromDate] And [Forms]![New Part Performance]![txtToDate]) AND ((vCUST_QUOTE_LINE.CUSTOMER_ID) Between [Forms]![New Part Performance]![CFrom] And [Forms]![New Part Performance]![CTo]));

The error message issued appears below.

Name: capture.jpg Views: 10 Size: 76.6 KB

Can anyone help me solve this issue?

Answer 1

Pull out the Select .. part and run that select query. Modify until it runs successfully.

Then either retry the append query or manually insert a set of values from the select query to locate the offending field(s)/value(s).