Filter in a report?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Randy - 31 Dec 2005 03:07 GMT

Access 2000. I have a report bound to a query. I have a field of
[TimeBase] and another field of [Hours] The result is a list of time base
codes and the corresponding hours to each code: Example: Time Base code:
01 Hours: 1.2, Time Base Code: 02 Hours 3.5, Time base Code: 04
Hours 1.0 Etc. This is fine but in another part of my report I need to
filter out only the time base code of 02 with the hours of 3.5 and multiply
this number by 6.00. How do I do this?..Thanks...Randy

Reply to this Message

Duane Hookom - 31 Dec 2005 03:20 GMT

"in another part of my report" means what? All parts of a report have a
section name. You can create filter totals with an expression. For instance,
if you want to sum the salaries of female employees with a job title of
"supervisor":

=Sum( Abs([Gender]="F" and [JobTitle]="Supervisor") * [Salary])

You could place an expression like this in a group or report header or
footer.

Signature

Duane Hookom
MS Access MVP
--

> Access 2000. I have a report bound to a query. I have a field of
> [TimeBase] and another field of [Hours] The result is a list of time base
[quoted text clipped - 3 lines]
> filter out only the time base code of 02 with the hours of 3.5 and
> multiply this number by 6.00. How do I do this?..Thanks...Randy

Reply to this Message

Randy - 31 Dec 2005 17:05 GMT

Iv'e tried this expression with no luck. =Sum(Abs([TimeBase]="02" And
[Hours])*6) I am getting a number of 12. I should be getting a number of
36.
On my report [TimeBase] has a code of "02" with a corresponding [Hours] of
6...Any Ideas where I have gone wrong..Thanks

> "in another part of my report" means what? All parts of a report have a
> section name. You can create filter totals with an expression. For
[quoted text clipped - 13 lines]
>> of 3.5 and multiply this number by 6.00. How do I do
>> this?..Thanks...Randy

Reply to this Message

John Spencer - 31 Dec 2005 18:52 GMT

I have to say your question is remarkably unclear.

Are you trying to Sum the Hours where the Timebase is equal to 02 and then
multiply that times 6?

= Abs(Sum((Timebase = "02") * 6 * [Hours]))

This takes advantage of False being zero or -1. So the boolean statement
Timebase="02" returns 0 or -1. Anything times zero is zero, so if timebase is
not "02", you are multiplying everything else in the equation by zero. Abs(...)
just gets the absolute value of the sum.

Hope this helps.

> Iv'e tried this expression with no luck. =Sum(Abs([TimeBase]="02" And
> [Hours])*6) I am getting a number of 12. I should be getting a number of
[quoted text clipped - 24 lines]
> >> of 3.5 and multiply this number by 6.00. How do I do
> >> this?..Thanks...Randy

Reply to this Message

Randy - 31 Dec 2005 18:59 GMT

Thanks John, your expression worked perfectly..Randy

>I have to say your question is remarkably unclear.
>
[quoted text clipped - 45 lines]
>> >> of 3.5 and multiply this number by 6.00. How do I do
>> >> this?..Thanks...Randy

Reply to this Message