Need to multiply if positive or negative

[kmclane]

I have a calculated field on my report that takes the value of a total from another field and multiplys it times a percentage. I have this code working fine:

=IIf([AgntPctProf]<"100",[TotProfLoss]*("." & [AgntPctProf]),[TotProfLoss])

Since I am taking a number entered in a table to figure a percent. I just relized that sometimes I will have a negative number in the TotProfLoss field, and when it is, I am getting the value of TotProfLoss instead of the calculation. How would I change this to take negative numbers into account, and still perform the calculation? Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[KenReay]

Hi
you have some very strange code there mixing strings and numbers, try:

=IIf(Abs([AgntPctProf])<100,[TotProfLoss]*([AgntPctProf]/100),[TotProfLoss])


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scriverb]

The ABS (absolute) function that will convert the negative number to postive but not affect postitive numbers. But, just what do you want to do if this field is negative. I am not sure what your options are. Please explain the calculation in a little more detail.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[kmclane]

OK, TotProfLoss is a currency field derived by subtracting total fees from total income. Each agent has a varying percent of profit as a residual. Some get 0 percent, some get 100 percent, some get 50 or 80, it depends on the contract. Sometimes the fees are more than the pincome, so you end up with negative numbers. Agents are also liable for loss to the same degree as profits. I have a table that holds the number of each agents percent of profit or loss. If that number is 100, I just carry the value of TotProfLoss down, if it is 50, I want to calculate 50%, and so on. My code works, but in the case of a loss, i.e. TotProfLoss = ($52.59), I should get by calcolation ($26.30), but for some reason I get ($52.59). I hope this clarifies some. Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

I found that somehow it stopped calculating at all. Then I remembered that I changed the data type of the field yesterday, removed the quotes from around the 100, and it works correctly. Thanks, for your help, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg