Criteria for calculated field

[TeeSee]

The following is a calculated field called "Markup" ... Markup:
(([NetSell]/[IPR])/([NetCost]))-1.
NetSell and NetCost are also calculated fields.
How can I set the criteria in the query grid to find Markup of less
than 25%.
I continually get a wee box asking me to "enter parameter value" for
NetSell and NetCost.

 

[Peter Doering]

The following is a calculated field called "Markup" ... Markup:
(([NetSell]/[IPR])/([NetCost]))-1.
NetSell and NetCost are also calculated fields.
How can I set the criteria in the query grid to find Markup of less
than 25%.
I continually get a wee box asking me to "enter parameter value" for
NetSell and NetCost.

Use the formulas instead of aliases:

Markup: (((FormulaToCreateNetSell)/[IPR])/((FormulaToCreateNetCost)))-1

 

[TeeSee]

The following is a calculated field called "Markup" ... Markup:
(([NetSell]/[IPR])/([NetCost]))-1.
NetSell and NetCost are also calculated fields.
How can I set the criteria in the query grid to find Markup of less
than 25%.
I continually get a wee box asking me to "enter parameter value" for
NetSell and NetCost.

Use the formulas instead of aliases:

Markup: (((FormulaToCreateNetSell)/[IPR])/((FormulaToCreateNetCost)))-1

Thanks Peter .... Have done that but now get the same wee box asking
for parameter values for fields within the formulae. The following is
the criteria with the formulae inserted as suggested. Calculated Sell,
CalculatedNet and LDCost ..... are all calculated fields.
((IIf([CurrSellPrice]=0,[CalculatedSell],[CurrSellPrice])/[IPR])/
(IIf([LDCostPerInvUnit]=0,[CalculatedNet],[LDCostPerInvUnit]))-1)<0.25

 

[Peter Doering]

The following is a calculated field called "Markup" ... Markup:
(([NetSell]/[IPR])/([NetCost]))-1.
NetSell and NetCost are also calculated fields.
How can I set the criteria in the query grid to find Markup of less
than 25%.
I continually get a wee box asking me to "enter parameter value" for
NetSell and NetCost.

Use the formulas instead of aliases:

Markup: (((FormulaToCreateNetSell)/[IPR])/((FormulaToCreateNetCost)))-1

Thanks Peter .... Have done that but now get the same wee box asking
for parameter values for fields within the formulae. The following is
the criteria with the formulae inserted as suggested. Calculated Sell,
CalculatedNet and LDCost ..... are all calculated fields.
((IIf([CurrSellPrice]=0,[CalculatedSell],[CurrSellPrice])/[IPR])/
(IIf([LDCostPerInvUnit]=0,[CalculatedNet],[LDCostPerInvUnit]))-1)<0.25

Review all field names again. If you are asked for parameters you have
either mistyped a field name or you still use an alias rather than a field
name. If you don't find it, post the entire SQL string.

 

[TeeSee]

TeeSee wrote:
The following is a calculated field called "Markup" ... Markup:
(([NetSell]/[IPR])/([NetCost]))-1.
NetSell and NetCost are also calculated fields.
How can I set the criteria in the query grid to find Markup of less
than 25%.
I continually get a wee box asking me to "enter parameter value" for
NetSell and NetCost.
Use the formulas instead of aliases:
Markup: (((FormulaToCreateNetSell)/[IPR])/((FormulaToCreateNetCost)))-1

Thanks Peter .... Have done that but now get the same wee box asking
for parameter values for fields within the formulae. The following is
the criteria with the formulae inserted as suggested. Calculated Sell,
CalculatedNet and LDCost ..... are all calculated fields.
((IIf([CurrSellPrice]=0,[CalculatedSell],[CurrSellPrice])/[IPR])/
(IIf([LDCostPerInvUnit]=0,[CalculatedNet],[LDCostPerInvUnit]))-1)<0.25

Review all field names again. If you are asked for parameters you have
either mistyped a field name or you still use an alias rather than a field
name. If you don't find it, post the entire SQL string.

Peter was unable to find my error. Here is the SQL.

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

Thanks for taking the time.

 

[Peter Doering]

Peter was unable to find my error. Here is the SQL.

You are still using aliases in formulas:

SELECT tblMaterialMaster.ManufacturerName,
[...]
IIf([...]) AS CalculatedNet, IIf([...]) AS LDCostPerInvUnit,

e.g. CalculatedNet and LDCostPerInvUnit are aliases.

IIf([LDCostPerInvUnit]=0,[...] ^^^^^^^^^^^^^^^^^^
[CalculatedNet],[LDCostPerInvUnit]) AS NetCost,

^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^
You have to repeat the original formulas, i.e. instead of ...

.... [CalculatedNet],[LDCostPerInvUnit]...

you have to use:

.... IIf([ListPrice]=0,0,IIf([Funds]="USD",
(([ListPrice]-([ListPrice]*[Discount]))*(1-[XtraDiscount])*(1+
[ExchangeRate])), ([ListPrice]-([ListPrice]*[Discount]))*(1-
[XtraDiscount]))), IIf([Funds]="USD",
(([CostPerInvUnit]*(1-[XtraDiscount]))*(1+[ExchangeRate])),
[CostPerInvUnit]) ...

These are not the only occurrences, just examples.

BTW, you have a table tblExRate that is not joined with any other table. My
guess is that you are using USD and CAN as currencies and ExchangeRate for
CAN=1. If that's the case you can join the table:

FROM (( tblMaterialMaster
INNER JOIN tblCustomerItems
ON tblMaterialMaster.SISItemCode = tblCustomerItems.SISItemCode )
INNER JOIN tblULFC
ON tblMaterialMaster.LocalGroup = tblULFC.LocalGroup )
INNER JOIN tblExRate
ON tblMaterialMaster.Funds = tblExRate.CurrencyID

(replace tblExRate.CurrencyID by the correct field name)

and instead of:

IIf([ListPrice]=0,0,IIf([Funds]="USD",(([ListPrice]-([ListPrice]*[Discount]))*(1-[XtraDiscount])*(1+[ExchangeRate])),([ListPrice]-([ListPrice]*[Discount]))*(1-[XtraDiscount])))
AS CalculatedNet,

use:

Abs([ListPrice]<>0)*(([ListPrice]-([ListPrice]*[Discount]))*(1-[XtraDiscount])*(1+[ExchangeRate]))
AS CalculatedNet,

(all aircode)