×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access 97 report with linked SQL Server Data

Access 97 report with linked SQL Server Data

Access 97 report with linked SQL Server Data

(OP)
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.

RE: Access 97 report with linked SQL Server Data

(OP)
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;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close