Query Expression

  • Thread starter Thread starter ladybug via AccessMonster.com
  • Start date Start date
L

ladybug via AccessMonster.com

Currently I have this expression in a query: expression: ([expr]-[chrPFP])*
(0.3)*([SumOfdecProductionHours])
There is another field in the query that is [chrProgramShortName]

I would like my expression as it is above, however for the
chrProgramShortName IF the entry is "CP4" I want the expression to be
different. For every 10 over the expr is over the chrPFP I want it to
mulitply the 0.3. So if the expr=40 and the chrPFP=20, then 0.3 would only
be multiplied twice to give 0.6, instead of 20 times to give 6.

How do I do this in my query? Sorry if this does not make sense. It was the
best way I could think of to describe what I need.
 
Use an IFF function call:

=IFF([chrProgramShortName = "CP4",
(([expr]-[chrPFP]) MOD 10)* (0.3)*([SumOfdecProductionHours])
([expr]-[chrPFP])* (0.3)*([SumOfdecProductionHours]) )

Hope that helps.

Sprinks
 
I think this would work, but I keep getting an error that says there are too
many closing parentheses.
Use an IFF function call:

=IFF([chrProgramShortName = "CP4",
(([expr]-[chrPFP]) MOD 10)* (0.3)*([SumOfdecProductionHours])
([expr]-[chrPFP])* (0.3)*([SumOfdecProductionHours]) )

Hope that helps.

Sprinks
Currently I have this expression in a query: expression: ([expr]-[chrPFP])*
(0.3)*([SumOfdecProductionHours])
Click to expand...
[quoted text clipped - 8 lines]
How do I do this in my query? Sorry if this does not make sense. It was the
best way I could think of to describe what I need.
Click to expand...
Click to expand...
 
I would try something like the following

=IIF([chrProgramShortName] = "CP4",
(1 + (Expr-ChrPFP)\10) *([expr]-[chrPFP])*
(0.3)*([SumOfdecProductionHours]),
([expr]-[chrPFP])* (0.3)*([SumOfdecProductionHours]) )

What if ChrPFP is larger than Expr? and therefore the difference is a
negative number?

What if the difference is 22 or 29? Does that mean in both cases you want
to multiply .3 * 2?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sprinks said:
Use an IFF function call:

=IFF([chrProgramShortName = "CP4",
(([expr]-[chrPFP]) MOD 10)* (0.3)*([SumOfdecProductionHours])
([expr]-[chrPFP])* (0.3)*([SumOfdecProductionHours]) )

Hope that helps.

Sprinks


ladybug via AccessMonster.com said:
Currently I have this expression in a query: expression:
([expr]-[chrPFP])*
(0.3)*([SumOfdecProductionHours])
There is another field in the query that is [chrProgramShortName]

I would like my expression as it is above, however for the
chrProgramShortName IF the entry is "CP4" I want the expression to be
different. For every 10 over the expr is over the chrPFP I want it to
mulitply the 0.3. So if the expr=40 and the chrPFP=20, then 0.3 would
only
be multiplied twice to give 0.6, instead of 20 times to give 6.

How do I do this in my query? Sorry if this does not make sense. It was
the
best way I could think of to describe what I need.
Click to expand...
Click to expand...
 
Back
Top