maximum calc. using iif statements

C

Carlee

Hi there,

Newsgroups were awesome for helping me calculate a billing rate for print
counts which are 130% of the target volume. I used this formula in my query
(it worked great):

DiscountTier2:
IIf([TargetVol_100]*(0.1)*(0.1)<IIf((([PrintCount]-(1.3)*([TargetVol_100]))*(0.1))>0,([PrintCount]-(1.3)*([TargetVol_100]))*(0.1),0),[TargetVol_100]*(0.1)*(0.1),IIf((([PrintCount]-(1.3)*([TargetVol_100]))*(0.1))>0,([PrintCount]-(1.3)*([TargetVol_100]))*(0.1),0))

Problem:
I want to create a discount of 12% for print volumes in excess of 140% of
Target
volume. In Excel, this works :

=max(0,(PrintCount - 1.4(TargetVol_100)) * .12))

I can't use the max function the same way in access. Can anyone help me
figure this out using an iif statement?

Many thanks in advance,
Carlee
 
M

MGFoster

Carlee said:
Hi there,

Newsgroups were awesome for helping me calculate a billing rate for print
counts which are 130% of the target volume. I used this formula in my query
(it worked great):

DiscountTier2:
IIf([TargetVol_100]*(0.1)*(0.1)<IIf((([PrintCount]-(1.3)*([TargetVol_100]))*(0.1))>0,([PrintCount]-(1.3)*([TargetVol_100]))*(0.1),0),[TargetVol_100]*(0.1)*(0.1),IIf((([PrintCount]-(1.3)*([TargetVol_100]))*(0.1))>0,([PrintCount]-(1.3)*([TargetVol_100]))*(0.1),0))

Problem:
I want to create a discount of 12% for print volumes in excess of 140% of
Target
volume. In Excel, this works :

=max(0,(PrintCount - 1.4(TargetVol_100)) * .12))

I can't use the max function the same way in access. Can anyone help me
figure this out using an iif statement?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In a query Max() means the maximum value of the indicated column.
Excel's Max() means return the maximum value of the indicate values in
the parentheses. To get what you want just use the same format you've
used in your original IIF() function:

IIf((([PrintCount]-(1.4)*([TargetVol_100]))*(0.12))>0,
(([PrintCount]-(1.4)*([TargetVol_100]))*(0.12)), 0)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQw9vWoechKqOuFEgEQLRZACfQ4PDnE0aMKNREVnCQ12swvw4e2UAn0m+
8nQwUFOIvBCUQeF78PrkiU6z
=KQGr
-----END PGP SIGNATURE-----
 
Top