Setting Criteria

[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.

 

[Guest]

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

Barry

 

[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.

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

 

[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;