IIf comparison

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to write an iif statement (or something else?) that will show a result
where if the first field is greater in value than the second field, it will
subtract the first from the second. But if the first field is less in value
than the second field, I need to reverse the equation to avoid negative
results. Example -2,000 in first field minus 70,000 in the second field.
Needing the result to be 68,000.

Thanks
 
Hi Kim

Sorry, I was too hasty. The previous formula will only work if both numbers
are negative or both are positive.

I think this formula will give a better result.

Expr1: IIf([Field1]>[Field2],[Field1]-[Field2],[Field2]-[Field1])

good luck

Trevor
 
How would I link this query to the report? I only know enough of Access to be
dangerous : )

Trevor Mills said:
Qreate a query with the following equation

Expr1: Abs(Abs([Field1])-Abs([Field2]))

Kim said:
I need to write an iif statement (or something else?) that will show a result
where if the first field is greater in value than the second field, it will
subtract the first from the second. But if the first field is less in value
than the second field, I need to reverse the equation to avoid negative
results. Example -2,000 in first field minus 70,000 in the second field.
Needing the result to be 68,000.

Thanks
Click to expand...
Click to expand...
 
That worked - thanks. Another question - how to show the result if the first
number is negative? Some of the results need to be negative, some do not.
Could be like the example I used earlier. Or could be that we've used more
supplies and have ended up in the hole. Can I use an "or" in an IIF?

Trevor Mills said:
Hi Kim

Sorry, I was too hasty. The previous formula will only work if both numbers
are negative or both are positive.

I think this formula will give a better result.

Expr1: IIf([Field1]>[Field2],[Field1]-[Field2],[Field2]-[Field1])

good luck

Trevor

Kim said:
I need to write an iif statement (or something else?) that will show a result
where if the first field is greater in value than the second field, it will
subtract the first from the second. But if the first field is less in value
than the second field, I need to reverse the equation to avoid negative
results. Example -2,000 in first field minus 70,000 in the second field.
Needing the result to be 68,000.

Thanks
Click to expand...
Click to expand...
 
Kim

If you have another question then you need to start a new question in the
forum. Make the subject as explicit and descriptive as possible. This is
becuase many new users of Access have similar questions and so other users
can benefit from the questions you have asked.

Kim said:
That worked - thanks. Another question - how to show the result if the first
number is negative? Some of the results need to be negative, some do not.
Could be like the example I used earlier. Or could be that we've used more
supplies and have ended up in the hole. Can I use an "or" in an IIF?

Trevor Mills said:
Hi Kim

Sorry, I was too hasty. The previous formula will only work if both numbers
are negative or both are positive.

I think this formula will give a better result.

Expr1: IIf([Field1]>[Field2],[Field1]-[Field2],[Field2]-[Field1])

good luck

Trevor

Kim said:
I need to write an iif statement (or something else?) that will show a result
where if the first field is greater in value than the second field, it will
subtract the first from the second. But if the first field is less in value
than the second field, I need to reverse the equation to avoid negative
results. Example -2,000 in first field minus 70,000 in the second field.
Needing the result to be 68,000.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top