aggregate function error again... 1

[DBServices]

I have 3 unbound combo boxes on a form. All three have multiple columns and the third column of each combo box has a price field. All but one column have a column width of 0 so the only field being shown is the "Name" field. In my forms footer section I have a text box that I want to show a total of all three combo boxes after they are selected. Now, they might not all have a selection so there might be a null value in one or more or in all of them. In my text box's Control Source I have =Sum([cboWith].[column](2)+[cboNoItem].[column](2)+[cboExtraItem].[column](2)) and this produces "#Error". I read in the help files that the sum function ignores null values so the nz function isn't needed although i have tried that for each text box in hopes of getting rid of the error but no such luck. =Sum(nz([cboWith].[column](2))+nz([cboNoItem].[column](2))+nz([cboExtraItem].[column](2))) was what I used and it's an error as well. I also tried =[cboWith].[column](2)+[cboNoItem].[column](2)+[cboExtraItem].[column](2) but that just concatenates the fields...Can someone PLEASE tell me what I am missing??? Thank you in advance...Dannie.

 

[MajP]

you can only use the sum function on Bound controls.

I would think
NZ([cboWith].[column](2),0)+NZ([cboNoItem].[column](2),0)+NZ([cboExtraItem].[column](2),0)

If that is still concatenating instead of adding them it sees the price as a string. Try

Val(NZ([cboWith].[column](2),0))+val(NZ([cboNoItem].[column](2),0))+val(NZ([cboExtraItem].[column](2),0))

 

[DBServices]

The second suggestion worked perfectly...Never knew you can only use the sum function on bound controls but thank you very much for that! Every thing is coming along great...D.

 

[MajP]

Yes the most common example of this is a continous form where you have something like price and quantity, and an unbound control of totalPrice which is =price*quantity. In the footer if you want the grand total you cannot do:
sum([totalPrice]) since it is unbound. The work around is to sum the individuals
sum([price]*[quantity])