Multiply Positive and Negative numbers in IIF statement

[Lowell]

I have searched for answer to this problem and seem to be dead ended.



I have a form with two text boxes (we won't worry now about where that info comes from)

One text box always has 0 or positive numbers (txtPositive) and the other text box has 0 or negative numbers (txtNegative)

I have another text box (txtAnswer) that calculates an answer from either of these two, depending on the positive or negative input recieved from elsewhere, with another text box (txtHours)

txtPositive is Currency format
txtNegative is Currency format
txtAnswer is Currency format
txtHours is General Number format

This is the formula in txtAnswer:

=IIf([txtPositive]>=0,([txtPositive]*[txtHours]),([txtNegative]*[txtHours]))

I could use this formula as well, but it doesn't seem to work any better:

=IIf([txtNegative]<0,([txtNegative]*[txtHours]),([txtPositive]*[txtHours]))

The problem is:

When there is a positive number in txtPositive everything is great, but a negative number in txtNegative makes the answer 0.

I used this formula in a completely different text box and it worked good:

=IIf([txtNegative]>0,0,([txtNegative]*[Hours]))

txtNegative = -$2.10
txtHours = 10
Answer = -$21.00

But this doesn't help because I won't have a calculation if there is a positive number in txtPositive.

Does anyone else have the same problem?
Is there a way to get around it somehow?

[ssanfu]

Can there be a positive number in [txtPositive] AND a negative number in [txtNegative] at the same time?? What should the answer be???

txtPositive = $4.20
txtNegative = -$2.10

txtHours = 10

Answer = ????

[Lowell]

No, there will never be a negative in txtNegative and a Positive in txtPositive at the same time.

It is set up so that it is 'either-or'.

Its the profit or loss per hour spent working.

[ssanfu]

You could try this:

=([txtPositive]*[txtHours])*Abs([txtPositive]>=0)+([txtNegative]*[txtHours])*Abs([txtNegative]<=0)

[ssanfu]

This also works:

=IIf([txtPositive]>0,([txtPositive]*[txtHours]),([txtNegative]*[txtHours]))

[Lowell]

Thanks alot, that works!

I saw the 'Abs' online and in help, but couldn't figure out how to get it to do what I wanted.

This will work

[Lowell]

The last suggestion you offered is what I thought should work, but it didn't.

If you look at the first post, I have the exact same code without the equals sign, and it didn't work.

I don't know why though

[ssanfu]

If you look at the first post, I have the exact same code without the equals sign, and it didn't work.

I copied the formula from your first post.

Then, in my testing, using an unbound form and unbound text box controls, it also didn't work correctly. After about 5 minutes of more testing and talking to myself, I realized I was entering a positive number in the negative amount text box. (I entered 2.1 instead of -2.1)

Also, I added code to ensure one text box was 0 when the other had a non zero number entered and other code to force the txtNegative text box to always be negative.(trying to reduce bonehead errors)


Glad you have it working...