Calculation Trouble

[ABreeze]

Can somebody please look at my calculation and tell me why it doesn't work.

tblPool stores a total amount of a grant
frmGrantRequest contains details of an application
frmGrantDetails is a subform on this form whose info gets stored in tblGrantDetails. This subform stores information on projects relevant to an application. An application may have several projects. I have totalled the amount awarded of each project to get an application amount.

What I need to do now is total all of the application amounts for a particular pool and subtract it from the PoolAmount.

Below is the formula as it is now but it totals all of the application totals regardless of the pool.
I think there might be a problem calculating the total cheque_Amount. Any ideas?

=DLookUp("[PoolAmount]","tblPool","[PoolID]
= " & [Forms]![frmGrantRequest]![PoolID])-DSum("[cheque_Amount]","tblGrantDetails","[PoolID]
= " & [Forms]![frmGrantRequest]![PoolID])

 

[Stew]

I suggest troubleshooting by breaking your calculation into two fields (DLookup in one, DSum in the other) and seeing if the numbers that result are correct, then doing the subtraction based on the two fields. It might help isolate where the problem is located.

Paul

 

[ABreeze]

I've tried that. The DLookup works fine. The problem is with the the second part. For some reason its totalling all of the cheques and not just the ones for the selected pool

OK now I know why that calculation isn't working. There is no poolId field on the tblGrantDetails. This is the table that stores data from my subform. The poolId field is on the main form and the table that references that is tblGrant. Wow, I'm more confused than ever. How do I calculate a sum from values on a subform with the linking field on the main form? This is the last thing that I have to get working and my database design is finished!!!Please help

 

[Rich]

Add an unbound textbox to the subform footer, set it's control source to =Sum([MyField]) to display it on the main form it's just =MyForm!MySubform!MyControl

 

[ABreeze]

I see what you're saying but that's not what I need. I've used that formula to calculate totals for each application and I have included it in the subform footer. I need now to calculate all of the applications that receive funding from a particular pool and have a running calculation that the user can use to find out how much of the funding pool has been used... I need to total more than the open subform. I need the total of all of the subforms whose main form has a particular poolID...Make any sense at all?

 

[Stew]

It does indeed make sense and DSum should do the trick for you as long as you can refernece the poolID. If the relationships between your tables are correct then you can include the poolID as part of the query for the subform (place the field on the subform if you like and hide it if you don't want it seen) and reference it in the criteria of your formula.

If it continues to give you problems, post your most recent formula.

Out of curiousity, what part of Newfoundland are you from?

Paul