Conditional Sum

[Claire]

I am working in a subform for a timesheet that includes # of Hours, and
currently have a summed total of the hours in the footer. There is also a
check box field for whether the hours are applied. I would like to add a
second conditional total which would sum the hours if they are applied.

I have tried DSum
DSum("[# Hrs]","tblHours",[Applied ?]=True)
and variations on that theme.

and Sum(Iff([Applied]<0, [# Hrs], 0))
and variations on that theme.

This form is based on a table, and each entry in the sub form contains
includes the timesheet ID that is in the main form.

Thanks for any suggestions!

 

[Steve Schapel]

Claire,

First of all, I should point out that it is not a good idea to use # or
? as part of the name of a field or control. Mind you, that is unlikely
to be related to your current problem.

The function is IIf() not Iff(). I think this should work:
=Sum(IIf([Applied],[# Hrs],0))

Alternatively:
=Abs(Sum([# Hrs]*[Applied]))

 

[Steve Sanford]

Hi Claire,

and Sum(Iff([Applied]<0, [# Hrs], 0))

The function is IIF(), not IFF(). Is the field [Applied] or [Applied ?] ??

<soap box>

It is not good programming practice to use special characters, spaces in
object names. Examples of special characters are ?,/,\,!,@,#,$,%,^,&,*.
Also, you should not use reserved words.

In a year or two from now, will you (or some one else) know what [# Hrs]
refers to?? work hours? Vacation, Sick leave, ST, OT,... Anyway,..

</soap box>


Hopfully the recordsource for the subform is a query. Add a calcualted field
to the query:

AppliedHrs: IIF([Applied], [# Hrs],0)


Add an unbound field to the footer section of the subform. Set the Control
source to

=Sum([AppliedHrs])

You might need add Me.Refresh to the form current event or the after
update event of the control bound to the field {Applied] to get the controls
in the footer to update if changes are made in the subform records.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I am working in a subform for a timesheet that includes # of Hours, and
currently have a summed total of the hours in the footer. There is also a
check box field for whether the hours are applied. I would like to add a
second conditional total which would sum the hours if they are applied.

I have tried DSum
DSum("[# Hrs]","tblHours",[Applied ?]=True)
and variations on that theme.

and Sum(Iff([Applied]<0, [# Hrs], 0))
and variations on that theme.

This form is based on a table, and each entry in the sub form contains
includes the timesheet ID that is in the main form.

Thanks for any suggestions!

 

[Claire]

Thanks everyone for your help. It's now working beautifully. Apparently my
past logic class stuck with me enough that I read IIF as IFF which I know as
if and only if.

For some theory insight, I keep reading that it is best to base forms off of
queries and not off of tables. Why is this? Is it so that you can keep the
tables free of functions but function to your delight in the queries? Does
access look at tables and queries in different ways?

If I am up for adding in a query and changing all the controls to that query
instead of to the table, will I just have to switch the control source for
each field or will other errors appear? Thanks again!

 

[BruceM]

For the purposes of setting a record source for a form or report, Access
pretty much looks at tables and queries the same way. Tables are
essentially buckets for holding data. Queries let you organize the data as
you choose, perform calculations, specify criteria, and in general organize
the data. For instance, if you have FirstName and LastName fields, you can
create another field:
FullName: [FirstName] & " " & [LastName]
or
LastFirst: [LastName] & ", " & [FirstName]

Another example is that you can specify as the criteria for a date field:
Between [Enter Start Date] And [Enter End Date]
The query, or any form or report based on the query, will prompt you for
Start Date and End Date, and will then limit the recordset to just the
records within the specified date range.

It is generally a good idea to limit the records you load to just the ones
you need. Queries are a powerful way of doing that.

If you have a form based on a table, just create a query based on that
table, and use the query instead of the table. You will not need to change
the control source of the text boxes, etc. in most cases, but you do have
the option of binding a text box to, say, FullName as described above.

I said that in most cases you will not need to change the control source,
but if you create a query that combines several tables (this is often done
for reports in particular) and there are two fields with the same name
Access needs to know the table as well as the field name for a control
source. The Control Source drop down list will include both options
(Table1.EmployeeID and Table2.EmployeeID, for instance).

There may be some issues when you change from a table to a query as the
record source, but these are not usually a problem, certainly not enough to
discourage the use of queries.