IF Statment

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having problems using an IF statment on a report. I'm trying to
calculate a value using only certain criteria. For example, in a text box I
have =IF([field in table],"name of criteria",sum([name of field]*number,"")
when I run the report I get a parameter box asking for IF.
 
This isn't a very clear question. "trying to calculate a value using only
certain criteria" is of little value. I can only assume you want to sum an
expression where another expression is true. If so, try
=Sum(Abs(YourTrueFalseExpression) * YourExpressionToSum)
 
I am having problems using an IF statment on a report. I'm trying to
calculate a value using only certain criteria. For example, in a text box I
have =IF([field in table],"name of criteria",sum([name of field]*number,"")
when I run the report I get a parameter box asking for IF.
Click to expand...

You cannot use If in a control source expression.
You can use IIf (Immediate If).
Let's assume the multiplier value is 15.

=Sum(IIf([FieldInTable] = "Some Text Criteria", Nz([SomeField],0) *
15))

Only records that match the criteria will be summed.
The above control cannot be placed in the Report Page Footer.
 
Thanks Fred

fredg said:
I am having problems using an IF statment on a report. I'm trying to
calculate a value using only certain criteria. For example, in a text box I
have =IF([field in table],"name of criteria",sum([name of field]*number,"")
when I run the report I get a parameter box asking for IF.
Click to expand...

You cannot use If in a control source expression.
You can use IIf (Immediate If).
Let's assume the multiplier value is 15.

=Sum(IIf([FieldInTable] = "Some Text Criteria", Nz([SomeField],0) *
15))

Only records that match the criteria will be summed.
The above control cannot be placed in the Report Page Footer.
Click to expand...
 
Okay, so you CAN'T use an IF/THEN statement in Access?!
If you *can*, how would you write it, because I thought of the same solution
to my problem.

BarryC said:
Thanks Fred

fredg said:
I am having problems using an IF statment on a report. I'm trying to
calculate a value using only certain criteria. For example, in a text box I
have =IF([field in table],"name of criteria",sum([name of field]*number,"")
when I run the report I get a parameter box asking for IF.
Click to expand...

You cannot use If in a control source expression.
You can use IIf (Immediate If).
Let's assume the multiplier value is 15.

=Sum(IIf([FieldInTable] = "Some Text Criteria", Nz([SomeField],0) *
15))

Only records that match the criteria will be summed.
The above control cannot be placed in the Report Page Footer.
Click to expand...
Click to expand...
 
Fredg's reply had the proper function "IIf()".

--
Duane Hookom
MS Access MVP


HeatherLou1974 said:
Okay, so you CAN'T use an IF/THEN statement in Access?!
If you *can*, how would you write it, because I thought of the same
solution
to my problem.

BarryC said:
Thanks Fred

fredg said:
On Tue, 1 Mar 2005 11:07:02 -0800, BarryC wrote:

I am having problems using an IF statment on a report. I'm trying to
calculate a value using only certain criteria. For example, in a text
box I
have =IF([field in table],"name of criteria",sum([name of
field]*number,"")
when I run the report I get a parameter box asking for IF.

You cannot use If in a control source expression.
You can use IIf (Immediate If).
Let's assume the multiplier value is 15.

=Sum(IIf([FieldInTable] = "Some Text Criteria", Nz([SomeField],0) *
15))

Only records that match the criteria will be summed.
The above control cannot be placed in the Report Page Footer.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top