Sum of Subform from a Query

[keiath]

Hi Guys I have this form below. As you can see there are Prepayments and Accruals

I need to add the amounts together based on its Description, i Set up a query so that it SumS by group

=[AccrualPreyments]![SumOfAmount]

But this doesnt work comes back as Name? Any Ideas

Site No	Description	Comments	Group Code	Expense Code	Amount
0272S	Prepayments		A	92	454.85
0272S	Prepayments		A	92	-454.85
0272S	Prepayments		A	87	139.85
0272S	Prepayments		A	87	-122.84
0272S	Accrual		A	55	-650.00
0272S	Accrual		A	55	1293.00
0272S	Accrual		A	64	1870.35
0272S	Accrual		B	65	78.82
0272S	Accrual		B	82	-16530.57
0272S	Accrual		B	82	8652.94
0272S	Accrual		B	82	455.77
0272S	Accrual		B	82	5478.5
0272S	Accrual

[MarvinM]

Yes. It looks like a misspelling of "AccrualPreyments"

[keiath]

spelling is correct so far as program is concerned (actual spelling not so much)

[MarvinM]

OK, so your form is named "AccrualPreyments". The Prepayments and Accruals show in the detail section of the report. What is "SumOfAmount"? Is that a control on the form where you want the total to show up? Is that in the footer?

[keiath]

I created a query to group 'Site Number' 'Amount' by Description

So the query returns:-

Prepayments £11.07
Accruals £-2452.36

Now i need these to display on the form (outside the subform)

As i cant figure a way of Suming withing subform (at the footer) to sum @Prepayments = x

If you know what i mean

[MarvinM]

Keiath,

So do you want the sum in the footer of the subform or on the main form?

You can do it in the footer of the subform.
Open the subform in design view.
Create a text box control in the footer.
For the Control Source of that box, enter this:

=DSum("[Amount]","NameOfSourceForSubform","[Description]=Prepayments")

Of course, you will need to provide the name of the table or query that is the source for the form. You can also Provide the Caption for the Label for this text box as something like "Sum of Prepayments:"

Then do the same for the accruals.

You can also do this on the main form, if you like, but it sounds like that is your second choice.

HTH
_________________
Regards,
Marvin M
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
-------------------------------------------------------------------------------------------------------------------
If this post has helped you, please click on the little sheriff badge in the lower left corner. Thanks!
-------------------------------------------------------------------------------------------------------------------

[keiath]

I tried this

=DSum("[Amount]"," [AccrualsPreyments]","[Description]=Prepayments")

In the footer of the subform

Then

=[Accruals subform].[Form]![Text14]

Outside the subform

but no luck

[MarvinM]

OK. In the DSum function,
DSum("[Amount]"," [AccrualsPreyments]","[Description]=Prepayments")

[Amount] is the name of the field that you want to sum.
[AccrualsPreyments] is the name of the source table to your subfrom.
[Description] is the name of the field that has the condition in it.

Is that correct? Let's try it with no spaces between the quotation marks. Like this: "AccrualsPreyments"
No spaces inside the quotes.
Does that give you a result in the footer of the subform?

[MarvinM]

Then on the main form (Outside the subform), you want to show that value again?
You have an unbound text box with this as the Control Source:
=[Accruals subform].[Form]![Text14]

Try the full format like this:
[Forms]![MainFormName].[Form]![NameOfSubformControl].Text14

Note that the object after "Form!" is the name of the control, or the container that holds your subform, and NOT the name of the subform. This assumes that Text14 is the name of the control on your subform in which you have your DSum formula.