Where statement causing "This expression typed incorrectly or too complex" error
I have a several sub queries that run to calculate some fields for me including teh field [GP%] which takes the difference between two fields and divides by one of them to get the percentage. Then they get pulled into a larger query that displays all of the information. The query works just fine and runs pretty quickly if I don't include any Where statement on it.
However, I want to add a Where statement to a field and when I do, I get the error: "This expression is typed incorrectly or too complex. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
The field I'm trying to add a criteria on is a field called [GP%]. In a subquery, I calculate this field by subtracting two other fields then dividing by it. I would like to add the Criteria [GrossProfit].[GP%] < .05
I am able to add criteria to other fields in this query that aren't calculated with division in a previous query. Any ideas?
I want to add:
I have a several sub queries that run to calculate some fields for me including teh field [GP%] which takes the difference between two fields and divides by one of them to get the percentage. Then they get pulled into a larger query that displays all of the information. The query works just fine and runs pretty quickly if I don't include any Where statement on it.
However, I want to add a Where statement to a field and when I do, I get the error: "This expression is typed incorrectly or too complex. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
The field I'm trying to add a criteria on is a field called [GP%]. In a subquery, I calculate this field by subtracting two other fields then dividing by it. I would like to add the Criteria [GrossProfit].[GP%] < .05
I am able to add criteria to other fields in this query that aren't calculated with division in a previous query. Any ideas?
Code:
SELECT [Invoice Entries - SE Numbers].Company, [Invoice Entries - SE Numbers].SEPfx, [Invoice Entries - SE Numbers].[SE Number], [Invoice Entries - SE Numbers].SIPfx, [Invoice Entries - SE Numbers].ShipmentNumber, [Invoice Entries - SE Numbers].ShipmentItem, [Invoice Entries - SE Numbers].InvoiceDate, [Invoice Entries - SE Numbers].Branch, [Invoice Entries - SE Numbers].InvoiceType, [Invoice Entries - SE Numbers].CustomerNumber, [Invoice Entries - SE Numbers].CustomerName, [Invoice Entries - SE Numbers].SalesCategory, [Invoice Entries - SE Numbers].InvoiceWeight, [Invoice Entries - SE Numbers].Value, [Invoice Entries - SE Numbers].Pcs, [Invoice Entries - SE Numbers].Weight, [Invoice Entries - SE Numbers].Repl, [Invoice Entries - SE Numbers].ReplUoM, [Invoice Entries - SE Numbers].Status, [Gross Profit].InternalMetalChargeValue, [Gross Profit].MetalCharge, [Gross Profit].MetalChargeUoM, [Gross Profit].Cost, [Gross Profit].CostUoM, [Gross Profit].GrossProfit, [Gross Profit].[GP%], [Gross Profit].[ReplGP%], [Net Profit].TotalCharge, [Net Profit].TotalCost, [Net Profit].NetProfit, [Net Profit].[NP%], [Total Net Profit].TotalInvoiceNP, [Total Net Profit].[TotalNP%], [Invoice Entries - SE Numbers].Desc1, [Invoice Entries - SE Numbers].Desc2
FROM (([Invoice Entries - SE Numbers] INNER JOIN [Gross Profit] ON ([Invoice Entries - SE Numbers].ShipmentItem = [Gross Profit].ShipmentItem) AND ([Invoice Entries - SE Numbers].ShipmentNumber = [Gross Profit].ShipmentNumber) AND ([Invoice Entries - SE Numbers].SIPfx = [Gross Profit].SIPfx) AND ([Invoice Entries - SE Numbers].[SE Number] = [Gross Profit].[SE Number]) AND ([Invoice Entries - SE Numbers].SEPfx = [Gross Profit].SEPfx) AND ([Invoice Entries - SE Numbers].Company = [Gross Profit].Company)) INNER JOIN [Net Profit] ON ([Invoice Entries - SE Numbers].ShipmentItem = [Net Profit].ShipmentItem) AND ([Invoice Entries - SE Numbers].ShipmentNumber = [Net Profit].ShipmentNumber) AND ([Invoice Entries - SE Numbers].SIPfx = [Net Profit].SIPfx) AND ([Invoice Entries - SE Numbers].[SE Number] = [Net Profit].[SE Number]) AND ([Invoice Entries - SE Numbers].SEPfx = [Net Profit].SEPfx) AND ([Invoice Entries - SE Numbers].Company = [Net Profit].Company)) INNER JOIN [Total Net Profit] ON ([Net Profit].[SE Number] = [Total Net Profit].[SE Number]) AND ([Net Profit].SEPfx = [Total Net Profit].SEPfx) AND ([Net Profit].Company = [Total Net Profit].Company);
I want to add:
Code:
WHERE ((([Gross Profit].[GP%])<"0.05"));