Error in Query How to fix

[Basshopper]

I have this query behind a form for data entry, Besides the entry data there is a calculated field. I am down to the last error message and would like some expert out there to see if they can see the problem. The error message is t_quote qty!qty 1. I cannot add that table to the query as then it does not let me enter data. Any suggestions on this code Thanks in advance


SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].

, [t_BOM Part Cross].[Ascentron #], [t_BOM Part Cross].Rev, [t_BOM Part Cross].Ref, [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Customers #], [t_BOM Part Cross].Description, [t_BOM Part Cross].MFG, [t_BOM Part Cross].[U/M], [t_BOM Part Cross].[Qty Per], [t_BOM Part Cross].[Use Part?], [t_BOM Part Cross].[Alt MFG #], [t_BOM Part Cross].[Cust Cost], [t_BOM Part Cross].[Alt Unit 1], [Qty Per]*[Alt Unit 1] AS [Unit 1 Ext], IIf([t_BOM Part Cross]![Min Qty]=0,0,IIf([t_Quote Qty]![Qty 1]*[t_BOM Part Cross]![Qty Per]=0,0,IIf([t_Quote Qty]![Qty 1]*[t_BOM Part Cross]![Qty Per]<[t_BOM Part Cross]![Min Qty],(([t_BOM Part Cross]![Min Qty]-[t_Quote Qty]![Qty 1]*[t_BOM Part Cross]![Qty Per])*[t_BOM Part Cross]![Alt Unit 1]),IIf(([t_Quote Qty]![Qty 1]*[t_BOM Part Cross]![Qty Per]/[t_BOM Part Cross]![Alt Pkg Size])=Int([t_Quote Qty]![Qty 1]*[t_BOM Part Cross]![Qty Per]/[t_BOM Part Cross]![Alt Pkg Size]),0,[t_BOM Part Cross]![Alt Pkg Size]-(((([t_Quote Qty]![Qty 1]*[t_BOM Part Cross]![Qty Per]/[t_BOM Part Cross]![Alt Pkg Size])-Int([t_Quote Qty]![Qty 1]*[t_BOM Part Cross]![Qty Per]/[t_BOM Part Cross]![Alt Pkg Size]))*[t_BOM Part Cross]![Alt Pkg Size])))*[t_BOM Part Cross]![Alt Unit 1]))) AS [XCS 1 Ext], [t_BOM Part Cross].[Alt Unit 2], [t_BOM Part Cross].[Alt Unit 3], [t_BOM Part Cross].[Alt Unit 4], [t_BOM Part Cross].[Alt Unit 5], [t_BOM Part Cross].[Alt Unit 6], [t_BOM Part Cross].[Alt Unit 7], [t_BOM Part Cross].[Alt Unit 8], [t_BOM Part Cross].[Alt Unit 9], [t_BOM Part Cross].[Alt Unit 10], [t_BOM Part Cross].[Alt Unit 11], [t_BOM Part Cross].[Alt Unit 12], [t_BOM Part Cross].[MIN LT], [t_BOM Part Cross].[MAX LT], [t_BOM Part Cross].[Min Qty], [t_BOM Part Cross].[Alt Pkg Size], [t_BOM Part Cross].[Lot Charge], [t_BOM Part Cross].[Lot Qty], [t_BOM Part Cross].NRE, [t_BOM Part Cross].ETF, [t_BOM Part Cross].Tooling, [t_BOM Part Cross].[Other NRE], [t_BOM Part Cross].Vendor, [t_BOM Part Cross].Stock, [t_BOM Part Cross].Comments
FROM [t_BOM Part Cross]
ORDER BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].

, [t_BOM Part Cross].[Line #];

 

[BNPMike]

Presumably you do not have the calculated field in the query. You need to update only single real fields in the base tables. You can calculate the field on the form but not in the query.

 

[Basshopper]

The quote qty is in another table and not in this query to start. I do need thoses fields to do the calculation in the form query. If I add the table then the query will not accept data entry. Are you saying to put this formula as a calculated field in the form vs the query???

 

[BNPMike]

Just looking at your SQL you seem to have a calculated field (XCS 1 Ext). You need to pick up the component fields in the query and then perform this calculation within the relevant form field. You will not be able to update that field but at least your query will be updatable.