Hello
How can i display a sum based on all category that are currently displayed?
Thank You
Hello
How can i display a sum based on all category that are currently displayed?
Thank You
The sum of what?
If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
Bob Fitzpatrick
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
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.
How is the form displayed. Single Form or Continuous Forms.
If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
Bob Fitzpatrick
Continuous form
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.
If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
Bob Fitzpatrick
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 #];
Try this in the form footer:
=Sum(IIf([Source of Payment] = "LocBox", [Cash]+[Check]+[Credit Card], 0))
I get this #Error
Make sure there's not a textbox named "Source of Payment", which there probably is and can confuse Access.
I only have a label and combo box with source of payment
Is that field perhaps a numeric ID field rather than the text you see?
yes it is a numeric field.
So test the number:
=Sum(IIf([Source of Payment] = 3 , [Cash]+[Check]+[Credit Card], 0))