 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / May 2006- and + values are incorrect
The following function: =Abs(Sum(([pres]="scr")*[gain])) Produces a positive result all the time, even if the answer should be negative. I tried the following: =Sum(([pres]="scr")*[gain]) and it returned a negative value when it should have been positive and positive when it should have been negative. PERHAPS what you are looking for is Abs(Sum([Pres] = "Scr")) * [Gain] That counts the number of time Pres = "Scr" and then multiplies that times the value of gain. If gain is positive you get a positive number, if gain is negative you get a negative number. Alternatively, you could take your last method and multiply it by -1 to switch the sign = -1 * Sum(([pres]="scr")*[gain]) > The following function: > [quoted text clipped - 9 lines] > and it returned a negative value when it should have been positive and > positive when it should have been negative. The following worked: = -1 * Sum(([pres]="scr")*[gain]) Can anyone tell me why I am returning the opposite value without multiplying my answer by -1? 'pres' is a text field 'gain' is a number field that may be a positive or negative value. > PERHAPS what you are looking for is > [quoted text clipped - 22 lines] > > and it returned a negative value when it should have been positive and > > positive when it should have been negative. > The following worked: > [quoted text clipped - 3 lines] > multiplying > my answer by -1? Internally, true values are stored as -1, false as 0. Since ([pres]=0) is either true or false, it returns 0 or -1. Tom Lake >> The following worked: >> [quoted text clipped - 6 lines] > Internally, true values are stored as -1, false as 0. > Since ([pres]=0) is either true or false, it returns 0 or -1. That should be ([pres]="scr") is either true or false of course. > Tom Lake Because you are muliplying by a negative number. [pres]="scr") returns -1 (true) or 0 (False). and Sum of all the -1 is a negative number. You could rewrite this to = Sum(IIF([pres]="scr",1,0)) * [Gain] or =Count(IIF([pres]="scr",1,Null)) * Gain > The following worked: > [quoted text clipped - 35 lines] >> > and it returned a negative value when it should have been positive and >> > positive when it should have been negative. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.