Using Minimum and maximum

[Carlee]

Hi there,

I am doing some billing calculations. What I need to do is have my query
select the minimum of two choices(the results of two calculations). I have
tried min(option1,option2) but I keep getting the error 'wrong number of
arguments'. Can someone help me out?

here is the calculation I am trying to write:
DiscountTierOne: min(TV*(.1)*(.08), max(0,[PrintCount]-(1.2)*(TV)) * (.08))

Can anyone help me with this monster?

Many kind regards in advance,
Carlee

 

[Douglas J. Steele]

The Min and Max functions in Access don't work on a set of values: they're
intended to be used in queries to determine the minimum (or maximum) value
of a particular field.

You're going to have to use the IIf function to compare the values.
Something along the lines of:

DiscountTierOne: IIf(TV*(.1)*(.08) < IIf((([PrintCount]-(1.2)*(TV)) * (.08))

0, ([PrintCount]-(1.2)*(TV)) * (.08), 0), TV*(.1)*(.08),

IIf((([PrintCount]-(1.2)*(TV)) * (.08)) >0, ([PrintCount]-(1.2)*(TV)) *
(.08), 0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there,

I am doing some billing calculations. What I need to do is have my query
select the minimum of two choices(the results of two calculations). I
have
tried min(option1,option2) but I keep getting the error 'wrong number of
arguments'. Can someone help me out?

here is the calculation I am trying to write:
DiscountTierOne: min(TV*(.1)*(.08), max(0,[PrintCount]-(1.2)*(TV)) *
(.08))

Can anyone help me with this monster?

Many kind regards in advance,
Carlee

 

[Allan Murphy]

Carlee

Add a colomn to your query as follows
min_max_result:min_max([tv],[printcount])

Then create a module as follows

function min_max(tv,printcount)
dim result_tv as double
dim result_print_count as double
dim temp_result as double

result_tv = TV*(.1)*(.08)
result_printcount=PrintCount]-(1.2)*(TV)) * (.08)

if result_tv < result_printcount then
temp_result =result_tv
else
temp_result =result_printcount
end if

min_max=temp_result
end function

You may need to correct any typing errors in the calculation

Also what will happen if both values are the same?

 

[Carlee]

Hi there,

For print counts which are between 120% and 130% of the target volume
(TargetVol_100) 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))

This worked great for discounting values at 130% of target volume. What I
want to do now is discount for print volumes in excess of 140% of Target
volume. In Excel, this works beautiful, but it won't work in Access. Can
anyone help me a bit more?

Excel function is: =max(0,(PrintCount - 1.4(TargetVol_100)) * .12))

How can I do the same thing in Access using iif statements?

Many thanks in advance,
Carlee

--
Carlee

The Min and Max functions in Access don't work on a set of values: they're
intended to be used in queries to determine the minimum (or maximum) value
of a particular field.

You're going to have to use the IIf function to compare the values.
Something along the lines of:

DiscountTierOne: IIf(TV*(.1)*(.08) < IIf((([PrintCount]-(1.2)*(TV)) * (.08))

0, ([PrintCount]-(1.2)*(TV)) * (.08), 0), TV*(.1)*(.08),

IIf((([PrintCount]-(1.2)*(TV)) * (.08)) >0, ([PrintCount]-(1.2)*(TV)) *
(.08), 0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there,

I am doing some billing calculations. What I need to do is have my query
select the minimum of two choices(the results of two calculations). I
have
tried min(option1,option2) but I keep getting the error 'wrong number of
arguments'. Can someone help me out?

here is the calculation I am trying to write:
DiscountTierOne: min(TV*(.1)*(.08), max(0,[PrintCount]-(1.2)*(TV)) *
(.08))

Can anyone help me with this monster?

Many kind regards in advance,
Carlee