Criteria problem

[Sharon Hague]

Dear All

I have created a query and within this query I have created a field to calculate a percentage from values in two other numeric fields.

This shows the correct information however, I want to add a criteria to this field so that it only shows records <=30 (less than 30%). No matter how this field is formatted whether it is as a percentage or as general, when I run the query it brings this field up like a parameter as though it doesn't recognise what I have entered in the criteria, resulting in no records being retrieved.

Could anybody hlep with this as I can't see any problem in my criteria.

Cheers.

 

[Mile-O]

Would be much easier if you posted the SQL of your query.

 

[Sharon Hague]

Get ready its a long one ......

SELECT ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.THIS_RECORD, ACCOUNTING_SYSTEM_STOCK_HISTORY.THIS_RECORD, ACCOUNTING_SYSTEM_STOCK_HISTORY.ACCOUNT_NUMBER, ACCOUNTING_SYSTEM_SALES_LEDGER.ACCOUNT_NAME, ACCOUNTING_SYSTEM_STOCK_HISTORY.TRANSACTION_DATE, ACCOUNTING_SYSTEM_STOCK_HISTORY.TRANSACTION_TYPE, ACCOUNTING_SYSTEM_STOCK_HISTORY.REFERENCE_NUMBER, ACCOUNTING_SYSTEM_STOCK_HISTORY.QUANTITY, ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.ITEM_NUMBER, ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.[BUYING_PRICE#1], ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.[BUYING_PRICE#2], ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.[BUYING_PRICE#3], ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.[BUYING_PRICE#4], ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.[BUYING_PRICE#5], ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.AVERAGE_BUYING_PRICE, ACCOUNTING_SYSTEM_STOCK_HISTORY.UNIT_PRICE, ACCOUNTING_SYSTEM_STOCK_HISTORY.ITEM_DISCOUNT_PCENT, [QUANTITY]*[AVERAGE_BUYING_PRICE] AS [TOTAL AVERAGE BUYING £], [QUANTITY]*[UNIT_PRICE]*(100-[ITEM_DISCOUNT_PCENT])/100 AS [TOTAL SELLING £], [TOTAL SELLING £]-[TOTAL AVERAGE BUYING £] AS [ITEM PROFIT], [ITEM PROFIT]/[TOTAL AVERAGE BUYING £]*100 AS [% PROFIT], ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.[SELLING_PRICE#1]
FROM (ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE INNER JOIN ACCOUNTING_SYSTEM_STOCK_HISTORY ON ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.THIS_RECORD = ACCOUNTING_SYSTEM_STOCK_HISTORY.PARENT_RECORD) INNER JOIN ACCOUNTING_SYSTEM_SALES_LEDGER ON ACCOUNTING_SYSTEM_STOCK_HISTORY.ACCOUNT_NUMBER = ACCOUNTING_SYSTEM_SALES_LEDGER.ACCOUNT_NUMBER
WHERE (((ACCOUNTING_SYSTEM_STOCK_HISTORY.ACCOUNT_NUMBER)="aghyds") AND ((ACCOUNTING_SYSTEM_STOCK_HISTORY.TRANSACTION_DATE) Between [Enter Date from] And [Enter Date to]) AND (([ITEM PROFIT]/[TOTAL AVERAGE BUYING £]*100)>=30))
ORDER BY ACCOUNTING_SYSTEM_STOCK_HISTORY.TRANSACTION_DATE, ACCOUNTING_SYSTEM_STOCK_HISTORY.REFERENCE_NUMBER;

 

[Sharon Hague]

My mistake - you will see in my SQL I have >=30 when it should read <=30, but this still doesn't make it work anyway

 

[FoFa]

I would revert your query back to the way ot was before. Then write a query against that query and pull those records you want (since the original query contains the calulated field).

 

[Sharon Hague]

Hi

Do you mean revert back to >=30 instead of using <=30?

Also I'm not sure how you mean to pull the records through by creating another query from the original as will I not still have this same problem?

 

[FoFa]

Take that calculated criteria out of the original query so it returns all.
Create another based on the first query and use the criteria 30 <= [% Profit] in that one.