Calculating two fields with a minimum value

[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.

 

[Jack Leach]

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)

 

[PennyB]

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.

 

[Graham Mandeno]

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

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.

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.

 

[John Spencer MVP]

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

 

[John W. Vinson]

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.

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

-(-Valuation\25000)

 

[John Spencer MVP]

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

 

[John W. Vinson]

Be careful John.

Integer divide will give you different results.

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

<d'oh!>

Quite true. How about just

1+[Valuationi]\25000

then?

 

[John Spencer]

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

<d'oh!>

Quite true. How about just

1+[Valuationi]\25000

then?

 

[Graham Mandeno]

Very nifty, John!

--
Cheers,
Graham

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