Choose higher value

[sponge]

Hi,

Is there a way to automatically choose a value such as 0 if the expression goes to a negative number?

E.g. I have a query that will subtract a numeric field from 10. If 10 - [numericfield] is less than 0, then I only want it to display 0. Otherwise, it can display the value if it is positive.

Any help would be much appreciated.

 

[uncle-lai]

In one of the fields in your query enter this:

NewValue:IIf(10>[numericfield], 10-[numericfield], 0)

explanation: ( - condition- , - value returned if True - , - value returned if False - )

Peter

 

[The_Doc_Man]

You can also use the Min or Max functions in VBA:

A = Max( 0, computed-value )

If computed-value goes negative, this returns 0.

You can also do Min( value, value, value....) to return the LOWEST value.

On my tax returns, I always use Max( 0, refund-amount ) for the amount to be refunded to me. (The method I use COULD return a negative refund amount...)

 

[uncle-lai]

Ha, that's a much simpler way, and is exactly what sponge wants - choose higher value.

Peter