Display Total on a form

[Ray67]

Hello



How can i display a sum based on all category that are currently displayed?



Thank You

[Bob Fitz]

The sum of what?

[Ray67]

providername	month	year	Cash	Cash Cleared	Check	Check Cleared	Credit Card	Credit Card Cleared	Source of Payment	Grand Total
John Doe	February	2012	0.00	FALSE	500.00	FALSE	0.00	FALSE	LocBox	500.00
John Doe	February	2012	0.00	FALSE	400.00	FALSE	0.00	FALSE	LocBox	400.00
John Doe	February	2012	20.00	FALSE	0.00	FALSE	45.00	FALSE	Billing Dept.	65.00
John Doe	February	2012	0.00	FALSE	1,000.00	FALSE	0.00	FALSE	Billing Dept.	1,000.00
			LocBox	Billing Dept.	EFT
			$ 900.00	$ 1,065.00	$ -
						I Would like show these totals based on what is currently displayed on the form

[Ray67]

What is in red i would like to add it to the form to show me sum based on what is displayed on the form.

[Bob Fitz]

How is the form displayed. Single Form or Continuous Forms.

[Ray67]

Continuous form

[Bob Fitz]

My suggestion would be to create a text box for each total in the forms footer section. Use DSum() function in each to get the total. The syntax for the DSum() function can be found in the help files. Getting the criteria correct may cause a problem.
What is the Record Source of the form. I assume this is a query or a SQL statement. Can you post the SQL statement or the SQL statement of the query.

[Ray67]

It's a Query

SELECT T_MonthlyTotals.[Systems Posted on], T_DocInfo.[Provider Name], T_MonthlyTotals.[Date of Posting From], Format([Date of Posting From],"mmmm") AS [Month], Format([Date of Posting From],"yyyy") AS [Year], T_MonthlyTotals.[Date of Posting To], T_MonthlyTotals2.[Date of RCPT], T_MonthlyTotals2.[RCPT #], T_MonthlyTotals2.Cash, T_MonthlyTotals2.[Cash Cleared], T_MonthlyTotals2.Check, T_MonthlyTotals2.[Check Cleared], T_MonthlyTotals2.[Credit Card], T_MonthlyTotals2.[Credit Card Cleared], T_MonthlyTotals2.[Source of Payment], [Cash]+[Check]+[Credit Card] AS [Grand Total], T_MonthlyTotals2.[All Cleared], T_MonthlyTotals2.Comments, T_MonthlyTotals2.[Reflected last Month]
FROM (T_MonthlyTotals INNER JOIN T_MonthlyTotals2 ON T_MonthlyTotals.ID = T_MonthlyTotals2.[ID Main Table]) LEFT JOIN T_DocInfo ON T_MonthlyTotals.[Provider Name] = T_DocInfo.ID
WHERE (((T_DocInfo.[Provider Name])=[Forms]![F_ReconciliationList].[providername]) AND ((Format([Date of Posting From],"mmmm"))=[Forms]![F_ReconciliationList].[month]) AND ((Format([Date of Posting From],"yyyy"))=[Forms]![F_ReconciliationList].[year]) AND ((T_MonthlyTotals2.[All Cleared])=No))
ORDER BY T_MonthlyTotals2.[Date of RCPT], T_MonthlyTotals2.[RCPT #];

[pbaldy]

Try this in the form footer:

=Sum(IIf([Source of Payment] = "LocBox", [Cash]+[Check]+[Credit Card], 0))

[Ray67]

I get this #Error

[pbaldy]

Make sure there's not a textbox named "Source of Payment", which there probably is and can confuse Access.

[Ray67]

I only have a label and combo box with source of payment

[pbaldy]

Is that field perhaps a numeric ID field rather than the text you see?

[Ray67]

yes it is a numeric field.

[pbaldy]

So test the number:

=Sum(IIf([Source of Payment] = 3 , [Cash]+[Check]+[Credit Card], 0))