If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Form with SubForm Totals
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? |
#3
|
|||
|
|||
Form with SubForm Totals
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. |
Thread Tools | |
Display Modes | |
|
|