Where statement causing "This expression typed incorrectly or too complex" error (1 Viewer)

[jobrien4]

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?

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:

WHERE ((([Gross Profit].[GP%])<"0.05"));

 

[CJ_London]

Re: Where statement causing "This expression typed incorrectly or too complex" error

first, using non alphanumeric characters in field and control names can cause problems resulting in errors such as you are experiencing. I recommend you remove them (inc spaces).

second if [Gross Profit].[GP%] is not text then you should not use the quotation marks

WHERE [Gross Profit].[GP%]<0.05

third, you might want to consider aliasing your tables - makes it much easier to read the code

SELECT IE.Company, IE.SEPfx, IE.[SE Number], ....
FROM (([Invoice Entries - SE Numbers] AS IE INNER JOIN [Gross Profit] AS GP.....

 

[jobrien4]

Re: Where statement causing "This expression typed incorrectly or too complex" error

first, using non alphanumeric characters in field and control names can cause problems resulting in errors such as you are experiencing. I recommend you remove them (inc spaces).

second if [Gross Profit].[GP%] is not text then you should not use the quotation marks

WHERE [Gross Profit].[GP%]<0.05

third, you might want to consider aliasing your tables - makes it much easier to read the code

SELECT IE.Company, IE.SEPfx, IE.[SE Number], ....
FROM (([Invoice Entries - SE Numbers] AS IE INNER JOIN [Gross Profit] AS GP.....

I did remove all special characters (%) and spaces from field names and also removed quotes from the 0.05 but am still receiving the error.

Any other suggestions?

 

[James Deckert]

Re: Where statement causing "This expression typed incorrectly or too complex" error

I would suggest you get rid of all fields except bare minimum (after making a copy of the query) and see if the Where works. If not, then post this simplified query.