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

Null propagates in arithmetical expressions, so where anything in the expression might be Null the Nz function should be called to return a zero in place of a Null, e.g.  Nz(Sum([comm]),0)

Answer 2

thanks for the reply. the DLookup worked which has brought up another problem. this expression and another I have in a query should give me the same result but they don't. there off by about 26.00. I decided to make a blank table add a couple entries and see if I see a math problem. with  =Sum([ProfitLoss])+Sum([comm])+DLookUp("InitialEquity","tblEquity") I got no result.  I found the the comm column was the problem. It doesn't have any entries. I tried =Sum([ProfitLoss])+SumNZ([comm])+DLookUp("InitialEquity","tblEquity") but gfot an error message #name!. How do I get it to ignore blank entries?

thanks again

Answer 3

You can only reference a column in a table directly if the table is a component of the form's recordset.  Otherwise you need to call the DLookup function:

DLookup("InitialEquity","tblEquity")