Filter in a report?

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

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
 
"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.
 
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

Duane Hookom said:
"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.
--
Duane Hookom
MS Access MVP
--

Randy said:
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
Click to expand...
Click to expand...
 
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
36.
On my report [TimeBase] has a code of "02" with a corresponding [Hours] of
6...Any Ideas where I have gone wrong..Thanks

Duane Hookom said:
"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.
--
Duane Hookom
MS Access MVP
--

Randy said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks John, your expression worked perfectly..Randy

John Spencer said:
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
36.
On my report [TimeBase] has a code of "02" with a corresponding [Hours]
of
6...Any Ideas where I have gone wrong..Thanks

Duane Hookom said:
"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.
--
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 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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top