using the sum function

Question

I have a form with a text box in it and the control source in the properties is : =Sum([Profit/Loss]).

I need to add another column to it and from what I have been able to find it should look like this :  =Sum([Profit/Loss]+[txtcomm]) but this gives me an error message In the text box. What am I doing wrong?

both columns are currency and I am trying to add the total of both columns.

thank you

Answer 1

thank you for your response. This is what I need. this looks familiar so I think I had something close to this before I asked for help.

Now I need 1 other thing.

I need to add the initial entry from tblEquity

so it would look like  =Sum([ProfitLoss]+[comm])+[tblEquity]![initialEquity] (this came from the expression builder) but this give me the #name! error.

tbl equity only has 2 columns equity ID and initial equity and there is only1 entry in this table. what am I missing now?

Answer 2

My understanding of =Sum([ProfitLoss]+[comm]) would be =sum([250.00] +[-110.00])

No, the expression would return the summation of the addition of the values in each row, not the addition of the summations of each column.  If there can be Nulls at either of the column positions in any rows then the addition in those rows would in each case return Null, as Null propagates in arithmetical operations.  The expression should be:

    =Sum(Nz([ProfitLoss],0)+Nz([comm],0))

Calling the Nz function is only necessary, however, if either of the columns can be completely empty, i.e. all Null.  If this is not possible you can use:

    =Sum([ProfitLoss])+Sum([comm])

This does return the addition of the summations of each column.

Answer 3

yes I need the summation. profitLoss ( I removed the "/" at several suggestions I didn't know it was reserved) is 250.00 and comm is -110.00. ProfitLoss has over 4000 entries, comm has 200 or so. I have a stat query that totals these columns and 250.00 and -110.00 are the numbers I need to add together

If I understand you from your explanation I would need the 4000 plus entries in both columns and need a 3rd column to give me the results.

that's not the case I need to total both columns and add that result and as I said I tried and I thought =Sum([ProfitLoss]+[comm]) should work. I also tried =Sum([ProfitLoss]-[comm]) and got the same result 360.00 I remember several years ago I needed to Nz to an expression to make it work with zeros or maybe it was with out zeros I don't remember so I thought maybe there need to be some special characters to make access read negative numbers as negative numbers. My understanding of =Sum([ProfitLoss]+[comm]) would be =sum([250.00] +[-110.00]) which should result with 140.00.

Also while I'm at it when we get this figured out I need to add an entry from another table to this expression so it would look like =sum(table1[profitLoss]+table1[comm]+table2[equity]). I know that all the punctuation isn't correct in this example but you get the idea.  I can figure out the proper punctuation If this is right. Am I on the right track and is this doable.

and again thanks for all your help!

Answer 4

Let's be sure that you really want a summation operation rather than an addition operation; the two are not the same.  Summation operates over an arbitrary number of values in a set; addition operates over a fixed number of distinct values.

The expression you are using at present returns the summation of result of the addition of two values, e.g. with rows:

x      y

2    -1

3    -4

4    -2

the addition of the values in each row gives us 1,-1 and 2 respectively, so the summation of  the result of the addition of the values, Sum(x+y) would therefore return 2.

Is this what you want, or do you simply wish to return the result of the addition of the two values in each row:

x      y    x+y

2    -1     1

3    -4    -1

4    -2     2

In the latter the Sum operator is not used at all; the operation is merely the addition of the values in each row.

If you are intending to sum the result of the addition of the values, I can see no reason why Sum([Profit/Loss]+[comm]) would return 360 where the summations of each of the values, Sum([Profit/Loss]) and Sum([comm]) return 250 and -110 respectively.  Are you absolutely sure that the actual value of the latter is a negative number, and not merely formatted as such.  If the actual values are both positive numbers then the operation should be subtraction rather than addition to return the result you expect.

Answer 5

Thanks for all your help everyone.

1st I didn't know "/" was reserve symbol I changed that.

txtComm is the name of the text box and comm is the control source.

I tried most of your suggestions before I asked for help.

I have been working on this for 4 years and haven't looked at the table in2 or more. The comm is always been a currency figure but never used it much and it never was in any calculation so I never gave it a thought but when none of your suggestions were working I went back and looked and I had it as a number. I changed it to currency and =Sum([Profit/Loss]+[comm]) works except for one problem. profitLoss is a positive number and comm is a negative but when I add them I get a positive number 250 + -110 should get 140 but I get 360. I've googled this but found little help

thanks