Criteria for calculated field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TeeSee - 29 Dec 2007 21:37 GMT

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.

Reply to this Message

Peter Doering - 29 Dec 2007 22:12 GMT

> The following is a calculated field called "Markup" ... Markup:
> (([NetSell]/[IPR])/([NetCost]))-1.
[quoted text clipped - 3 lines]
> 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

Signature

Peter Doering [MVP Access]

Reply to this Message

TeeSee - 30 Dec 2007 14:57 GMT

> > The following is a calculated field called "Markup" ... Markup:
> > (([NetSell]/[IPR])/([NetCost]))-1.
[quoted text clipped - 10 lines]
> --
> Peter Doering [MVP Access]

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

Reply to this Message

Peter Doering - 30 Dec 2007 16:42 GMT

>>> The following is a calculated field called "Markup" ... Markup:
>>> (([NetSell]/[IPR])/([NetCost]))-1.
[quoted text clipped - 14 lines]
> ((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.

Signature

Peter Doering [MVP Access]

Reply to this Message

TeeSee - 31 Dec 2007 15:13 GMT

> >>> The following is a calculated field called "Markup" ... Markup:
> >>> (([NetSell]/[IPR])/([NetCost]))-1.
[quoted text clipped - 23 lines]
>
> - Show quoted text -

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.

Reply to this Message

Peter Doering - 01 Jan 2008 11:36 GMT

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

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)

Signature

Peter Doering [MVP Access]

Reply to this Message