Query Expression

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

 

[Guest]

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]

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])

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

 

[John Spencer]

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

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])
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.