Running Totals on Mainform

  • Thread starter Thread starter Linda Ribbach
  • Start date Start date
L

Linda Ribbach

I have a main form and a subform.

The subform is in datasheet view.

There is a checkbox on each record.

When I click the checkbox, I want a running dollar total to appear on the
mainform when the checkbox is checked.

I only want the sum of the dollar totals for the records whose checkboxes
are checked to appear.

I've been able to make the total of the subform's dollars to appear, but I
don't know what to do to make only the "records checked" to appear.

Thanks in advance
 
You can add an additional field to your query like such for example:

Say you a query
select name,amount,checkboxvalue from mytable;

add an additional field like this

select
name,amount,checkboxvalue,(amount*(iif(isnull(checkboxvalue),false,checkboxv
alue))*(-1) as checkedvalue from mytable;

since a false is = 0 and a true is = -1 the resulting chechedvalue field
will contain zeros when not checked. So sum this field instead.

HTH

Ken
 
I assume that this SQL should be in the subform's query.

Thanks, I'll try it.

Linda
 
It worked great. If you could, would you explain what this code does? what
does the (-1) do etc?
Is this it? (If the product of the Checkboxvalue and the amount is null then
(false or 0 ) otherwise the checkboxvalue then multiply by -1?)
Linda
 
you got it,

you have to check for null to avoid calculation problems. if you don't it
can return null for the sum even if only one field is null.

The multiply by -1 is to convert the true value (a number that is
actually -1) to a positive 1. ( to be more accurate false is always zero,
true is any non-zero value, but, MS is kind enough to treat it as minus one
on a yes no field). If you don't use the -1 then it just is a negative
result.

By the way have you ever wanted to run more than one summation on one or
more fields in the same query? You can do this by adding a conditional
equation that returns a true/false and then multiply by the field and -1.
One query, summation on multiple fields with multiple conditions. Real fast.

HTH

Ken
 
Thanks I think I understand. You were a great help.
I've never had to run more than one summation and I'm not sure I would
recoginize when to use your solution if had to run more than one summation.
By the way, how did you learn how to do all this?

Linda
 
Back
Top