Setting Criteria

T

TeeSee

Is it possible to set criteria for a "calculated field" in a select
query? When I try it I get an input box of sorts asking for me to input
values for some other field used in the "calculation".
Please advise.
 
B

Barry Gilbert

It is possible to do what you're asking. If you're having trouble, please
post your sql.

Barry
 
F

fredg

Is it possible to set criteria for a "calculated field" in a select
query? When I try it I get an input box of sorts asking for me to input
values for some other field used in the "calculation".
Please advise.
Click to expand...

An example of what you want to do would be nice.
 
T

TeeSee

Here is the SQL. If I set my criteria on one of the calculated fields i
get this "box" asking for input.

SELECT tblMaterialMaster.SISItemCode, tblMaterialMaster.[Material
description], tblMaterialMaster.ListPrice, tblMaterialMaster.InvUnit,
tblCustomerItems.SellUnit, tblCustomerItems.SellDiscFromList,
tblMaterialMaster.Discount, tblCustomerItems.XtraDiscount,
tblMaterialMaster.CostPerInvUnit, tblCustomerItems.CurrSellPrice,
tblCustomerItems.PriceDate, tblCustomerItems.IPR,
tblExRate.ExchangeRate,
IIf([Funds]="USD",(([ListPrice]-([ListPrice]*[Discount]))*(1+[ExchangeRate])*(1-[XtraDiscount])),((([ListPrice]-([ListPrice]*[Discount]))*(1-[XtraDiscount]))))
AS CalculatedNet,
IIf([Funds]="USD",[CostPerInvUnit]*(1+[ExchangeRate]),[CostPerInvUnit])
AS LDCostPerInvUnit,
IIf([CostPerInvUnit]=0,[CalculatedNet],[LDCostPerInvUnit]) AS NetCost,
(([NetSell]/[IPR])/([NetCost]))-1 AS Markup, [Markup]/([Markup]+1) AS
Margin, IIf([CurrSellPrice]=0,[CalculatedSell],[CurrSellPrice]) AS
NetSell,
IIf([Funds]="CAN",([ListPrice]-([ListPrice]*[SellDiscFromList])),(([ListPrice]-([ListPrice]*[SellDiscFromList]))*(1+[ExchangeRate])))*[IPR]
AS CalculatedSell, tblMaterialMaster.LocalGroup,
tblCustomerItems.CustomerIDCode, tblCustomerItems.CustItemCode,
tblMaterialMaster.Inactive
FROM tblExRate, tblMaterialMaster INNER JOIN tblCustomerItems ON
tblMaterialMaster.SISItemCode = tblCustomerItems.SISItemCode
WHERE (((IIf([CurrSellPrice]=0,[CalculatedSell],[CurrSellPrice]))>20)
AND ((tblMaterialMaster.Inactive)=No))
ORDER BY tblMaterialMaster.LocalGroup;
 
Top