Access 97 report with linked SQL Server Data

[Dad0AEW]

I have a report in an Access 97 app that has as its data source a query that is built programmatically. The query can get rather long, as the Where clause is built programmatically and can have several different conditions. When the data was all Access I was able to run it without error. I now have most of the tables as linked tables to a SQL Server 2005 database. When I now run the same report I get the following message:

"Run-time error '3163': The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

Is this because of the data now being linked to a SQL Server database? Could there be something in the SQL to the query (which now has straight Access and SQL linked tables in it) that would generate this error? When I step through the report the error occurs when the datasource Database value is set to CurrentDb.

Thank you for any assistance.

 

[dhookom]

There is at least one reply to the same question at Forum701

Duane
Hook'D on Access
MS Access MVP

 

[Dad0AEW]

This is my query. The Select and From clauses are the same, yet the WHERE clause is built programmatically based upon what the user selects on a screen.

SELECT DISTINCTROW I.TAG, K.Tag AS KTag, I.GAUGE, K.MetalEval, (IIf(I.TYPE="EMBSD" Or I.TYPE="MTRLAM" Or I.TYPE="MIXED" Or I.TYPE="TRANS" Or I.TYPE="HRPD" Or K.MetalEvalWord Is Null Or K.MetalEvalWord="",I.TYPE,K.MetalEvalWord)) AS ITYPE, I.TYPE, I.REASON, I.[MILL TAG], I.[THICKNESS ID], K.MaxRB, I.RB, I.ANCHO, I.[THICKNESS ID], K.Scale, I.WEIGHT, I.REMARKS, I.PIW, I.CANDIDATE, I.TESTING, I.PRICE, I.CUSTOMER, I.[SALES ORDER], I.[DATE SHIPPED], I.[CUST PURCHASE ORDER], I.[PROC ORDER], I.[RELEASE NUMBER], I.[DATE RELEASED], I.DOCUMENT, I.[DATE RECEIVED], I.[DATE ENTERED], CInt(([Weight]/[PCS])/[ANCHO]) AS NewPIW, I.ORIGEN, I.[TRANSACTION DATE], I.LOCATION, I.[ADDED BY], I.[DATE CHANGED], I.[OUR PURCHASE ORDER], I.[RECEIVING NUMBER], I.QUALITY, I.TYPEREC, I.[PROC COST], I.[SERV CENTER], I.[CHANGED BY], I.PHOTO, RoundNear(([Price]+(IIf(IsNull([FREIGHT COST]),0,[FREIGHT COST]))),1) AS RoundedPrice, I.[THICKNESS OD], [GAUGE ORDER].[SORT ORDER], I.LENGTH, I.PCS, I.[DATE RECEIVED], I.LIST, I.[FREIGHT COST], I.WAREHOUSE, I.[BASE PRICE], tblRMY.RMY_Code, Mid(I.[REASON],1,2) AS Expr5, CUSTOMERDATA.[BROKER CODE], Left(I.Remarks,(InStr(1,I.Remarks,"Kieh")-2)) AS InvRem, I.Selected, TagComments.KiehRemarks, TagComments.BranninRemarks, I.[PRICE]+I.[FREIGHT COST] AS DeliveredPrice, ([DeliveredPrice]*I.[WEIGHT])/100 AS EXTENSION
FROM ((((InvData AS I LEFT JOIN qryKiehMains AS K ON I.TAG = K.Tag) LEFT JOIN CUSTOMERDATA ON I.CUSTOMER = CUSTOMERDATA.CUSTOMER) LEFT JOIN tblRMY ON I.ORIGEN = tblRMY.Origin) INNER JOIN [GAUGE ORDER] ON I.GAUGE = [GAUGE ORDER].GAUGE) LEFT JOIN TagComments ON I.TAG = TagComments.Tag
WHERE (I.TAG <> '-999' AND (REASON) IN ("AVAILABLE") AND (WAREHOUSE) IN ("13", "16", "18") AND ((IIf(I.TYPE='EMBSD' OR I.TYPE='MTRLAM' OR I.TYPE='MIXED' OR I.TYPE='TRANS' OR I.TYPE='HRPD' OR K.MetalEvalWord Is Null OR K.MetalEvalWord = '',I.TYPE,K.MetalEvalWord))) IN ("GL", "GLNL", "HR", "HRPD", "HRPO", "MIXED", "TRANS") ) OR ((I.TAG) IN('505603', '505763'))
ORDER BY I.TAG, I.ANCHO;