How to use MIN statement in Access

[Guest]

I am trying my hand at a photo copier (xerox) meter billing program. I have
managed to conquer it in Excel, but am having difficulty in using it in a
query in Access where it ultimately must go.
IIf([Total copies]>[Includes # of copies],Min([Total copies]-[Includes # of
copies]-4000),"0"). The "4000" is where the problem seems to be.
The contract reads: $ 15 p.m. which includes 400 copies, 400 > 4000 copies @
..035 cents, 4000 > 8000 copies @ .032 cents, 8000 + copies @ .037 cents.
Your advice will be much appreciated.

 

[John Nurick]

Hi Horse,

The MIN() function in a query is totally different from the Excel MIN()
worksheet function: it;s an aggregate functioun returns the lowest value
of the expression in any record in the table, not just the lowest-valued
argument.

Instead, use nested IIFs with ordinary comparisons. When building a
complex expression, it's often easiest to do it in pieces and in a VBA
procedure which you can run from the Immediate Pane. Here's an
expression which (with the actual field names or values substituted)
will I think do your job; and below is the VBA procedure I used to help
nut it out.

IIf(Copies > T3, T3 * R3, 0) + IIf(Copies > T2, (IIf(Copies > T3, T3,
Copies) - T2) * R2, 0) + IIf(Copies > T1, (IIf(Copies > T2, T2, Copies)
- T1) * R1, 0)


Sub TestRates(Copies As Long)
Dim T1 As Long, T2 As Long, T3 As Long
Dim R1 As Double, R2 As Double, R3 As Double

'Variables for intermediate values
Dim O3 As Double, O2 As Double, O1 As Double

'Set parameters
T1 = 400
T2 = 4000
T3 = 8000
R1 = 0.035
R2 = 0.032
R3 = 0.037

'If over top threshold, its simple:
O3 = IIf(Copies > T3, T3 * R3, 0)

'If over second threshold, and over third, the number is the
'number between the two thresholds, otherwise the number over the
second.
O2 = IIf(Copies > T2, (IIf(Copies > T3, T3, Copies) - T2) * R2, 0)

'and so on
O1 = IIf(Copies > T1, (IIf(Copies > T2, T2, Copies) - T1) * R1, 0)

Debug.Print Copies, O3, O2, O1, O3 + O2 + O1
End Sub

I am trying my hand at a photo copier (xerox) meter billing program. I have
managed to conquer it in Excel, but am having difficulty in using it in a
query in Access where it ultimately must go.
IIf([Total copies]>[Includes # of copies],Min([Total copies]-[Includes # of
copies]-4000),"0"). The "4000" is where the problem seems to be.
The contract reads: $ 15 p.m. which includes 400 copies, 400 > 4000 copies @
.035 cents, 4000 > 8000 copies @ .032 cents, 8000 + copies @ .037 cents.
Your advice will be much appreciated.