Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1499
  • Last Modified:

Access Form - running total for records with checked boxes

Hi,
I have hopefully a relatively simple question.
I have a form that displays results from a query in Access.

I created a textbox that totals my "amount" field by placing the formula "=Sum([amount])" for the Control Source.

What I actually need is a running total for the records that are "Assigned"

so to look something like
=Sum([amount]) where [Assigned] = 1

Open in new window



I tried the above formula but it didnt work. Can somebody point me in the right direction

see attached
Form View
0
damixa
Asked:
damixa
  • 5
  • 4
1 Solution
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Try:

=Sum(Nz([Assigned],0) = 1, [amount], 0)

Open in new window



If the check box is bound to a Yes/No data type field then use this:

=Sum(Nz([Assigned],0) = True, [amount], 0)

Open in new window

Note: for Yes/no data type fields false = 0 and True = -1


Also see my example:
Batch Printing and Save As PDF
The Selector form counts check boxes plus some other things that might be helpful
0
 
damixaAuthor Commented:
I'm not sure what I am doing wrong, but it is giving me "The Expression you entered has a function containing the wrong number of arguments
0
 
damixaAuthor Commented:
see error
error
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Jeffrey CoachmanCommented:
1. running sums are tricky in forms.
In a report it is easy: Copy the Amount control and set the running sum property to: Over All

2. <where [Assigned] = 1>
In Access a checkbox will return 0 if false, and -1 when true
The good thing here is that the actual rule is:
Zero=False=Off
Any other value=True=On
(Oddly I don't see any values in your image that are: Assigned=yes
0
 
damixaAuthor Commented:
Well, the reason being is that I only pull fields that are unchecked. Once I check them and save the form, they will not show up again, they are (Assigned)
0
 
Jeffrey CoachmanCommented:
I think you are confusing the term "running sum" with "Sum" (or in your case,: Grand Total).
A running sum will display the cumulative amount for *each record*
A "Sum" (or Grand total) will only display once


So if you were doing this: =sum(Amount), ...then this is a grand total (sum), not a running sum.

So in order to get the sum (total) of only assigned records, use something like this:
=Dsum("amount", YourTableName","Assigned=True")

Is this what you wanted?

JeffCoachman
0
 
Jeffrey CoachmanCommented:
FWIW, a true "running sum" of Assigned records, in a form would look like this:
Running sum
The recordsource for the form would be something like this:
SELECT YourTable.RecordID, YourTable.TransDate, YourTable.DrawerID, YourTable.Amount, YourTable.Assigned, DSum("amount","YourTable","RecordID<=" & [RecordID] & " And " & "Assigned=True") AS RunSum FROM YourTable WHERE (((YourTable.Assigned)=True)) ORDER BY YourTable.RecordID;

;-)

JeffCoachman
0
 
Jeffrey CoachmanCommented:
sorry my syntax above was missing  character, it should be:

=Dsum("amount", "YourTableName","Assigned=True")
0
 
damixaAuthor Commented:
thank
0
 
Jeffrey CoachmanCommented:
;-)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now