Calculate if larger than

[Heger]

I have a field "Number" I want to calculate like this in my report:

If the number in "Number" is less than three, I will add X
If the number in "Number" is larger than three, I will add X+ (("Number"-3)*Y)

Can anyone help me with this?

 

[Duane Hookom]

You can use IIf() like:

=IIf([Number]<3, [Number]+[X], [X]+(([Number]-3)*[Y]))

It isn't clear whether X and Y are constants or fields. Also, your
description didn't consider [number] that equal 3.

 

[fredg]

I have a field "Number" I want to calculate like this in my report:

If the number in "Number" is less than three, I will add X
If the number in "Number" is larger than three, I will add X+ (("Number"-3)*Y)

Can anyone help me with this?

=IIf([NumField]<3,[NumField] + X,IIf([NumField]>3, X +(([NumField]
-3)*Y),"Your NumField was exactly 3. What do you want to do?"))

NOTE:
Number is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Al Campagna]

Heger,
Use an unbound calculated field to display the calculation. Don't save a calculation
if can be derived at any time later by the values you already have stored.
**Also... don't name your field "Number"... that's a reserved word in Access, and it will
cause problems. Better to name it something more meaningful anyway... like LoanAmount, or
OnHandQty, etc... (I'll use OnHandQty)
= IIF([OnHandQty] < 3, [OnHandQty] + [X], IIF([OnHandQty] > 3, [X] + (([OnHandQty]-3) *
Y, "")

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."