Re: Two different totals from one field depending on criteria

It is not unusual for one bad aggregate expression to mess
up the others. Stay concentrated on the one we've been
trying to work out.

If Sum(Hours) worked, it implies that either your syntax is
incorrect (double check the parenthesis, etc) or that Day is
not the name of the **field** in the form's record source
table/query (double check the spelling of the field names).
Or maybe the Hours field in the table is a Text field (or
the foirm's record source is a query that somehow converted
Hours to Text.

If that doesn't clarify things for you, please post a
Copy/Paste of the week1 text box control source expression
along with a Copy/Paste of the form's record source query's
SQL view.
--
Marsh
MVP [MS Access]


SherryScrapDog wrote:

Thanks. I think I get it now as far as the field versus the control. My
text boxes are in the form footer and I already had (and still have) a text
in the footer for =Sum(Hours) and that worked fine. Now, none of my totals
are working. I will keep trying. Again, thanks because I have more info
than I did! Sherry

"Marshall Barton" wrote:

Be sure you understand the difference between a field in a
table/query and a control on a form/report. A control can
be bound to a field in the form/report's record source and a
lot of pepole get confused and call the control a field.
This usually doesn't cloud comunications too much because
the resr of the sentence usually provides enough clues to
figure out which meaning is being discussed. In your case,
as for a lot of others, it is critical to understand that
the aggregate functions (Count, Sum, Avg, etc) only operate
on fields, they are unaware of controls.

It is alos important to understand that a text box control
with an aggregate expression need to be in a header/footer
section (but not the page header/footer).

You did not post the expression you used, but I think the
Week1 textbox expression should be like:

=Sum(IIf(Day > 20, Hours, 0))


SherryScrapDog wrote:
Thanks for this info! It makes sense to me, but I'm doing something wrong
as I get error as a result. My fields employee, day and hours are all in
the SQL statement for the form; is this what you mean, and are these the
fields you mean when you say they must be in the form's record source table
query? Maybe I need more explantion here?

Then, on the form, for my Week1 textbox, I put the formula (replacing Hours
for somefield) in the Control expression. Maybe this is not right either
because you say not to use a Textbox, but I don't know where else to put the
formula.

I'm sorry for more questions. I sure need to learn more. If you can help
more, thanks! Sherry

"Marshall Barton" wrote:

SherryScrapDog wrote:

I have an employee scheduling database that schedules employees for a 2-week
period. I keep track of the days of the weeks by using 'Days' 11-17 (week 1,
day 1 thru day 7) and 'Days' 21-27 (week 2, day1 thru day 7). I have a
schedule screen for an employee that shows all the days and timeslots they
are scheduled for with a total for the hours at the bottom. However, now the
user would like to have separate totals by week (not a separate schedule,
just the totals). I tried some things but am not experienced enough to
figure out how to get one total (Day < 20) and another total (Day > 20) to
display on the screen. Is this possible? I assume VBA code, but can't
figure out exactly how.


You can use this kind of expression in a form/report
header/footer section to get totals for the all the records
in the form/report. A critical requirement here is that the
fields must be in the form/report's record source table
query, not text box controls in the form/report.

=Sum(IIf(Day < 20, 1, 0))
will count the number of records where the Day field is less
than 20. To conditionally sum a field, you can use:
=Sum(IIf(Day < 20, somefield, 0))

This kind of expression also works in a totals type query.

.

Relevant Pages

  • Re: trouble with SUM function - Repost
    ... calculations in the record source query. ... calculation to be done in the report's record source query. ... Totals query to calculate the group total and then Join the ... I named this control txtProjectTotal. ...
    (microsoft.public.access.reports)
  • Re: Two different totals from one field depending on criteria
    ... FROM Home INNER JOIN (Employee INNER JOIN (INNER JOIN Calendar ON ... the foirm's record source is a query that somehow converted ... Copy/Paste of the week1 text box control source expression ... Now, none of my totals ...
    (microsoft.public.access.formscoding)
  • Re: Two different totals from one field depending on criteria
    ... table/query and a control on a form/report. ... fields you mean when you say they must be in the form's record source table ... user would like to have separate totals by week (not a separate schedule, ... You can use this kind of expression in a form/report ...
    (microsoft.public.access.formscoding)
  • Re: Two different totals from one field depending on criteria
    ... I think I get it now as far as the field versus the control. ... Now, none of my totals ... fields you mean when you say they must be in the form's record source table ... You can use this kind of expression in a form/report ...
    (microsoft.public.access.formscoding)
  • Re: form records not stored in table
    ... My recommendation was to change the list boxes to ... MS Access MVP ... field in the table that acts as record source, ... Control Source: ...
    (microsoft.public.access.forms)