Formula for report total not working (see too complex for me post)

  • Thread starter Thread starter RandyH
  • Start date Start date
R

RandyH

I have a report with the fields AccountNumber and LaborHours on it, both
field types are number. I would like to totalize LaborHours for certain
AccountNumber's. I was given a formula for my report footer like this:
=Sum(ABS(AccountNumber=3260)*Val([LaborHours]))
The problem is that the report prompts me for a value for LaborHours instead
of totalizing LaborHours for that AccountNumber, whatever I type in is what
is displayed on the report. I can supply more information if needed (just
don't know what's relevant).

Thanks, Randy
 
RandyH said:
I have a report with the fields AccountNumber and LaborHours on it, both
field types are number. I would like to totalize LaborHours for certain
AccountNumber's. I was given a formula for my report footer like this:
=Sum(ABS(AccountNumber=3260)*Val([LaborHours]))
The problem is that the report prompts me for a value for LaborHours instead
of totalizing LaborHours for that AccountNumber, whatever I type in is what
is displayed on the report. I can supply more information if needed (just
don't know what's relevant).
Click to expand...

If you're prompted for something, it means that it is not
the name of a field in the report trcord source table/query.
 
Marshall Barton said:
RandyH said:
I have a report with the fields AccountNumber and LaborHours on it, both
field types are number. I would like to totalize LaborHours for certain
AccountNumber's. I was given a formula for my report footer like this:
=Sum(ABS(AccountNumber=3260)*Val([LaborHours]))
The problem is that the report prompts me for a value for LaborHours instead
of totalizing LaborHours for that AccountNumber, whatever I type in is what
is displayed on the report. I can supply more information if needed (just
don't know what's relevant).
Click to expand...

If you're prompted for something, it means that it is not
the name of a field in the report trcord source table/query.
Click to expand...

Amazing what one letter will do! Your right, I misspelled LaborHours.
Interesting problem I didn't count on though; I am trying to segregate the
totals for six account numbers (3260, 3270, 3280, 3265, 3275), which the
above formula does. But now I need to totalize every other [LaborHours]
field EXCEPT for those six AND every one that ends in a 2 (an account number
ending in 2 designates overtime). Know of a way to make that work?
 
RandyH said:
I have a report with the fields AccountNumber and LaborHours on it, both
field types are number. I would like to totalize LaborHours for certain
AccountNumber's. I was given a formula for my report footer like this:
=Sum(ABS(AccountNumber=3260)*Val([LaborHours]))
The problem is that the report prompts me for a value for LaborHours instead
of totalizing LaborHours for that AccountNumber, whatever I type in is what
is displayed on the report. I can supply more information if needed (just
don't know what's relevant).
Click to expand...
"Marshall Barton"wrote
If you're prompted for something, it means that it is not
the name of a field in the report trcord source table/query.
Click to expand...
Click to expand...
RandyH said:
Amazing what one letter will do! Your right, I misspelled LaborHours.
Interesting problem I didn't count on though; I am trying to segregate the
totals for six account numbers (3260, 3270, 3280, 3265, 3275), which the
above formula does. But now I need to totalize every other [LaborHours]
field EXCEPT for those six AND every one that ends in a 2 (an account number
ending in 2 designates overtime). Know of a way to make that work?
Click to expand...


This might be better done with a subreport based on a Totals
(Group By) query.

Anyway, you can use the same kind of logic for this Sum too.

=Sum(IIf(Account Not In(3260, 3270, 3280, 3265, 3275) And
Right(Account, 1) <> "2", LaborHours, 0))
 
Back
Top