Calculating two fields with a minimum value

  • Thread starter Thread starter PennyB
  • Start date Start date
P

PennyB

I am multiplying two fields together, but if the value is less that .50 I
need it to equal .50. Currently I am using the control source expression
building to calculate the two fields "=[Valuation]*[Factor]".

Any suggestions would be helpful.
 
Try an immediate If statement...

=Iif([Valuation]*[Factor] < 0.5, 0.5, [Valuation]*[Factor])

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Thank you so much. It works perfect. I have another one for you if you can
help me.

I have a fee that needs to calculate on Valuation. The catch is it is $1.00
per every $25,000 or fraction there of. So if the valuation is less than
$25,000 it will be $1.00 and if it is $25,001 then it is $2.00.

Any suggestions on how to calculate this one?

Thanks again.
 
Hi Penny

If it's in a query, then this should work:

Fee: Int(Valuation/25000)-(Int(Valuation/25000)<>(Valuation/25000))

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

PennyB said:
Thank you so much. It works perfect. I have another one for you if you
can
help me.

I have a fee that needs to calculate on Valuation. The catch is it is
$1.00
per every $25,000 or fraction there of. So if the valuation is less than
$25,000 it will be $1.00 and if it is $25,001 then it is $2.00.

Any suggestions on how to calculate this one?

Thanks again.


PennyB said:
I am multiplying two fields together, but if the value is less that .50 I
need it to equal .50. Currently I am using the control source expression
building to calculate the two fields "=[Valuation]*[Factor]".

Any suggestions would be helpful.
Click to expand...
Click to expand...
 
Evan easier. The following will do it in a query or in VBA or ...

-Int(-Valuation/25000)

and if you want the fee to be some other multiple just multiply the above by
that number.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Evan easier. The following will do it in a query or in VBA or ...

-Int(-Valuation/25000)

and if you want the fee to be some other multiple just multiply the above by
that number.
Click to expand...

I'll see you an Int and raise you an integer divide:

-(-Valuation\25000)
 
Be careful John.

Integer divide will give you different results.

The user wants 2 if Valuation is 25001, your expression returns 1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Be careful John.

Integer divide will give you different results.

The user wants 2 if Valuation is 25001, your expression returns 1
Click to expand...

<d'oh!>

Quite true. How about just

1+[Valuationi]\25000

then?
 
As long as there is no decimal portion, I think that will work.

24999.51 will return 2 instead of 1. Integer division converts the
numbers to integer values before doing the arithmetic and then converts
the result to an integer value

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

Be careful John.

Integer divide will give you different results.

The user wants 2 if Valuation is 25001, your expression returns 1
Click to expand...

<d'oh!>

Quite true. How about just

1+[Valuationi]\25000

then?
Click to expand...
 
Very nifty, John!
:-)
--
Cheers,
Graham

John Spencer MVP said:
Evan easier. The following will do it in a query or in VBA or ...

-Int(-Valuation/25000)

and if you want the fee to be some other multiple just multiply the above
by that number.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
 
Back
Top