iif statement help

[lda via AccessMonster.com]

Hello,
I am having trouble with my iif statement.

the goal of the statement is simply: to equate charge. this is done in two
ways. if singleprice is checked yes than multiply the number of parameters by
price and also by the price multipier. If single price is not checked than
charge equals only price times price multiplier.

currenlty i am getting back only 1 and -1.

Any help is appreciated,

LDA

<statement>
charge: IIf([singleprice]=Yes,Count([param])*[testprices].[price]*
[pricemultiplier]) Or IIf([singleprice]=False,[testprices].[price]*
[pricemultiplier])

 

[John Spencer]

Your expression has parentheses in the wrong place.

IIf([singleprice]=Yes
,Count([param])*[testprices].[price]*[pricemultiplier])

The first IIF has ended and is going to return a number or null

Or

IIf([singleprice]=False,[testprices].[price]*[pricemultiplier])
This is a second IIF and is going to return a number or null.

Then the OR operator will combine those to return true or false.
I think you want something along the lines of the following. Although
I'm not sure that Count(Param) will work for you in this context.

IIF(SinglePrice=True,Price*PriceMultiplier*Count(Param),Price*PriceMultiplier)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[lda via AccessMonster.com]

Thanks john for the reply,
I placed the update statement into the criteria and worked okay. However, i
did receive an awful lot of results. Not sure why. Also why is it no a good
ideal to do count param... do you have any other suggestions.

thanks,

lda

<SQL>
SELECT DISTINCT Results.OrderID, SampleDetails.Matrix, Customers.CustomerName,
Results.Test, Results.Param, SampleDetails.Priority, Results.SampleNumber,
SampleDetails.AnalysisEmployee, SampleDetails.AnalysisTime, Tests.Cost,
Priorities.PriceMultiplier, Duke_OrderInfo.SampleType, Duke_OrderInfo.
StatCode, Results.Method, TestPrices.Price, Results.Report,
Duke_Accounting_TestCharge.Singleprice, IIf([singleprice]=No,Count([param])*
[testprices].[price]*[pricemultiplier]) AS charge2, IIf([singleprice]=Yes,
[testprices].[price]*[pricemultiplier]) AS charge, Results.orderid
FROM Duke_Accounting_TestCharge INNER JOIN ((((((Results INNER JOIN Orders ON
Results.OrderID = Orders.OrderID) INNER JOIN Customers ON Orders.CustomerID =
Customers.CustomerID) INNER JOIN SampleDetails ON (Results.OrderID =
SampleDetails.OrderID) AND (Results.SampleNumber = SampleDetails.SampleNumber)
AND (Results.Test = SampleDetails.Test)) INNER JOIN (Tests INNER JOIN
TestPrices ON (Tests.Method = TestPrices.Method) AND (Tests.Test = TestPrices.
Test)) ON (SampleDetails.Test = Tests.Test) AND (SampleDetails.Matrix = Tests.
Matrix)) INNER JOIN Priorities ON SampleDetails.Priority = Priorities.
Priority) INNER JOIN Duke_OrderInfo ON Results.OrderID = Duke_OrderInfo.
OrderID) ON (Duke_Accounting_TestCharge.Method = Tests.Method) AND
(Duke_Accounting_TestCharge.Test = Tests.Test) AND
(Duke_Accounting_TestCharge.Matrix = Tests.matrix)
GROUP BY Results.OrderID, SampleDetails.Matrix, Customers.CustomerName,
Results.Test, Results.Param, SampleDetails.Priority, Results.SampleNumber,
SampleDetails.AnalysisEmployee, SampleDetails.AnalysisTime, Tests.Cost,
Priorities.PriceMultiplier, Duke_OrderInfo.SampleType, Duke_OrderInfo.
StatCode, Results.Method, TestPrices.Price, Results.Report,
Duke_Accounting_TestCharge.Singleprice, Results.orderid, Results.param
HAVING (((SampleDetails.AnalysisTime) Is Not Null) AND ((Results.Report)=True)
) OR (((Results.Method) Not Like "V_*"))
ORDER BY Results.SampleNumber;

Your expression has parentheses in the wrong place.

IIf([singleprice]=Yes
,Count([param])*[testprices].[price]*[pricemultiplier])

The first IIF has ended and is going to return a number or null

Or

IIf([singleprice]=False,[testprices].[price]*[pricemultiplier])
This is a second IIF and is going to return a number or null.

Then the OR operator will combine those to return true or false.
I think you want something along the lines of the following. Although
I'm not sure that Count(Param) will work for you in this context.

IIF(SinglePrice=True,Price*PriceMultiplier*Count(Param),Price*PriceMultiplier)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello,
I am having trouble with my iif statement.

[quoted text clipped - 14 lines]

[pricemultiplier]) Or IIf([singleprice]=False,[testprices].[price]*
[pricemultiplier])