Form with SubForm Totals

In the Subform Query Builder, I have the following field:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [AcctID])))

AcctID is on the MainForm.

I have tried the following expressions with no success:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [Forms]![MedicalForm]![AcctID])))
AND
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[Forms]!
[MedicalForm]![AcctID] = " & [Forms]![MedicalForm]![AcctID])))

In other words, I want to show the total BillPayAmt for each AcctID.

Can someone show me what I'm doing wrong?

Much simpler than that. First add a footer to your subform. Now if your
subform is in continuous form view, you are ready to go.

Set the controlsource of a textbox in the footer to:

= Sum([BillPayAmt])

For a datasheet, you'll need to add a textbox to the main form, and set it's
controlsource to read the subform textbox:

= Forms!YourFormName!YourSubformControlName.Form!Tex tboxName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"iamnu" wrote in message
...
In the Subform Query Builder, I have the following field:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [AcctID])))

AcctID is on the MainForm.

I have tried the following expressions with no success:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [Forms]![MedicalForm]![AcctID])))
AND
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[Forms]!
[MedicalForm]![AcctID] = " & [Forms]![MedicalForm]![AcctID])))

In other words, I want to show the total BillPayAmt for each AcctID.

Can someone show me what I'm doing wrong?

On Feb 1, 8:45*am, "Arvin Meyer [MVP]" wrote:
Much simpler than that. First add a footer to your subform. Now if your
subform is in continuous form view, you are ready to go.

Set the controlsource of a textbox in the footer to:

= Sum([BillPayAmt])

For a datasheet, you'll need to add a textbox to the main form, and set it's
controlsource to read the subform textbox:

= Forms!YourFormName!YourSubformControlName.Form!Tex tboxName
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

"iamnu" wrote in message

...

In the Subform Query Builder, I have the following field:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [AcctID])))

AcctID is on the MainForm.

I have tried the following expressions with no success:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [Forms]![MedicalForm]![AcctID])))
AND
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[Forms]!
[MedicalForm]![AcctID] = " & [Forms]![MedicalForm]![AcctID])))

In other words, I want to show the total BillPayAmt for each AcctID.

Can someone show me what I'm doing wrong?

Thanks for the reply, Arvin.
I already have those totals on my Subform. But what I want to do is
to get sums from BillPayAmt that belong to the AcctID, then another
for those that belong to the PayerID and another for those that belong
to the DrID.

On the MainForm, I want to see "Grand Totals" for each Acct, each
Payer, and each Dr.