Understanding iif statement

[Matt]

Hi there,

I'm trying to document a database that someone else has created. I'm going
through each query step-by-step to identify how each part works. I've come
to a stumbling block with the following statement:

Pen_%: IIf([Disc_%]*[Disc_Factor]>1,1,[Disc_%]*[Disc_Factor])

MSDN for IIf says iif(evaluation, truepart, falsepart)

OK, so given a disc_% of 9.02% and a Disc_Factor of 2.85 I'm checking if
9.02% x 2.85 is greater than 1. If it is, return 1 otherwise return the
result.

I think it's not working correctly as access is giving Pen_% as 25.71% which
is greater than 1 so 1 should be returned right? I've tried forcing the
evaluation order with parenthesis.
It could be that I don't quite understand how the percentage data type is
handled.

Many thanks for your time.

 

[peregenem]

25.71% which
is greater than 1

No, 25.71% (or 0.2571) is less than one.

 

[Nikos Yannacopoulos]

Matt,

The parentheses will not help you, there is no order to force; you only
have one calculation on the LHS of the evaluation. This is not the problem.

Actually there is no problem, Access is doing the right thing.: 9.02% is
actually 0.0902, so 9.02% x 2.85 = 0.25707, displayed 25.71%. This is
obviously < 1, thus the condition evaluates to false, and the false part
(i.e. the calculation result) is returned.

Suggestion: forhet all about the percentages, if they are confusing,
work with plain onld fashioned decimals (data type Single or Double)
where 0.0902 x 2.85 = 0.25707 is straightforward.

HTH,
Nikos

 

[Matt]

No, 25.71% (or 0.2571) is less than one.

omg what a fool.. Thank you. You know when you've been staring at something
so long....

 

[peregenem]

if they are confusing,
work with plain onld fashioned decimals (data type Single or Double)
where 0.0902 x 2.85 = 0.25707 is straightforward.

.... or indeed the new-fangled but native DECIMAL data type i.e.

SELECT TYPENAME(0.0902 * 2.85)

returns 'Decimal'

 

[Nikos Yannacopoulos]

Provided one's working with A2K2 or A2K3. I have A2K3 but I'm in a mixed
environment, most of my users are still on A2K so I'm still in an A2K
mindset

Interesting: while SQL SELECT TYPENAME(0.0902 * 2.85) indeed returns
Decimal, ?TYPENAME(0.0902 * 2.85) in the immediate window returns Double!!!

Nikos

 

[peregenem]

Interesting: while SQL SELECT TYPENAME(0.0902 * 2.85) indeed returns
Decimal, ?TYPENAME(0.0902 * 2.85) in the immediate window returns Double!!!

That is because, in lieu of explicit casting, VBA assumes your values
are Double. Try:

? TypeName(CDec(0.0902) * 2.85)

and you should get Decimal.

For further interest, in SQL:

SELECT TYPENAME(5 / 2), TYPENAME(5 * 0.5), TYPENAME(5 / 0.5)

returns Double, Decimal, Decimal respectively.

 

[Nikos Yannacopoulos]

Thanks.

Nikos Yannacopoulos wrote:




That is because, in lieu of explicit casting, VBA assumes your values
are Double. Try:

? TypeName(CDec(0.0902) * 2.85)

and you should get Decimal.

For further interest, in SQL:

SELECT TYPENAME(5 / 2), TYPENAME(5 * 0.5), TYPENAME(5 / 0.5)

returns Double, Decimal, Decimal respectively.