Calculation problems

[potts]

Got two problems.

First.

I have an unbound text box that I want to calculate the sum of values in a subform. I have entered the expression as follows:

=Sum([SubTotal])

[SubTotal] being the field to be totaled. However, the result keeps coming up #Name. Anyone know how to sort this? I might mention that [SubTotal] is also a calculated field.


Second.

On another form I have an unbound text box that counts the number of entries in a subform

=Count([CustomerID])

CustomerID is half of the Primary Field, the other half being ProgramID.

The count works fine. However, I then want another unbound textbox to divide cost by the count. [Cost] is a calculated and unbound field. So I have tried:

=[Cost]/[Count]

[Cost] and [Count] being the two fields the calculation is to be performed on. The problem is that:

1. I get the wrong value returned ... e.g. £40/2people = £2.2222

2. If the count is changed, i.e. a record added or deleted, the change is not reflected in the latter calculation.

any ideas on how I might sort this mess out?

 

[Adrianna]

Potts,
I know that this might sound a little strange, but I've always had the most success by breaking down the calculations. The Name# usually happend when you're requesting a calculation on something that the database doesn't know it exists. Sub Total is not a bound field...therefore it doesn't know where to pull the total of sub total from. Especially because there is no sequence of events. If the systems wants to give you a Total (Grand-total) from the sub totals...it might not have resolved the sub total feild yet. This is the same concept with yout count/cost feild.....instead of trying to reuse the count and sub total unbound boxes....right out the whole expression:

Control Name Total
Control Source =Sum(Sum[fields that give you the sub total])

So, you don't have to pull your hair out...just write out the long expression and the system will give you less grief.....it's a little more effort on your part to redefine the variables...but in the long run....a lot less hassel!!

Good Luck!

 

[pono1]

Potts,

Hope you don't mind if I throw in a quick example. Here's a formula entered in a text box on a main form that counts the records in its linked subform:

=DCount("[transid]","tblTrans","ReceiveID = [Forms]![FrmRec]![ReceiveID]")

transid = a field (long number) in the table that holds the records behind the subform

tblTrans = the table that holds the records behind the subform

ReceiveID = field (long number, primary key, in the table behind the main form) linking the main and subform

FrmRec = main form

Regards,
Tim

 

[potts]

Thanks for the help. It now all works fine - relief