How do i add/subtract records conditionally??? 2

[smiley0q0]

Here is an example of my table:
code cd Amount
484 - 4,548
444 + 2,451
426 - 5,487
481 - 145
474 + 1,748
424 - 3,984

i need to add or subtract several of these amounts, if the cd is + then i need to subtract them, if the cd is - then i need to add them. given the string
444 426 474 484 ... it mathmatically comes out to
-2,451 + 5,487 - 1,748 + 4,548... = 5,836
your help is very much appreciated
thank you..

 

[CFields]

Something like this can do the trick
Private Sub MCN_AfterUpdate()
If Me![DOCTYPE] = 2 Then
Me![MCN] = Me![MCN] + 0.1
Else
Me![MCN] = CInt(Me![MCN])
End If
End Sub
This adds one and reverse to subtract or something like below depending on your own needs, again this is just examples

If Me![DESC] Like &quot;*thread*&quot; And Me![ITEMS] <> 1 Then
msgbox &quot;When a thread Stock Number is selected the Items can only be Qty 1 !!&quot;
Me![ITEMS] = 1

Hope this helps to give you ideas

 

[JerryDennison]

Assuming that all values of Amount are positive and that cd is a string you can use the following in a query to sign the Amount.

SignedAmount: IIF([cd] = &quot;-&quot;, -1*[Amount], [Amount])

 

[dneufarth]

or a query akin to Jerry's post

SELECT Sum(IIf([cd]=&quot;+&quot;,-1*[amt],[amt])) AS Sum
FROM tblTest
WHERE (((tblTest.code)=&quot;123&quot) OR (((tblTest.code)=&quot;456&quot) OR (((tblTest.code)=&quot;678&quot);

Dave

 

[smiley0q0]

does that work when i need to add/subtract 8 or 9 or more together? if so how? sorry, i am fairly new at this... all values of the amount are positive. it's the cd that makes the amount positive or negative. jtayl76@associatescommerce.com

 

[JerryDennison]

Using a query with the field I outlined above. Change the query to a Totals query and set the total property of this field to Sum.

 

[smiley0q0]

ok, that looks like it will work, i'll try it when i get into work tomorrow.. thanks!! jtayl76@associatescommerce.com

 

[MichaelRed]

I'm (AGAIN!) obviously missing something.

I thought i saw (a pkiit cat?) -- No! No! -- It was a conditional clause in the original posting.

jTaylor1074 only wanted to do the 'bassackwards' sum of four of the records? Specified as:

[red]
444 426 474 484 ... it mathmatically comes out to
-2,451 + 5,487 - 1,748 + 4,548... = 5,836
[/red]

So, I proceded to 'solve' it for only the codes posted.

Oh, well. Total waste. Soloution to NON/WRONG-PROBLEM.

Actually, TWO wrong soloutions. One w/ code. One w/ code/sql.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[smiley0q0]

thank you Jerry, it works great! :-Q i appreciate your help JTAYL76@AssociatesCommerce.com or jtaylor1076@yahoo.com

 

[JerryDennison]

You're welcome! (*)