Hi Ashley,
you're mixing up two things, it seems to me: nulls and zero values, which
are not at all the same thing. Look at your statement:
IIf(
[TR_PRICING].[Form]![TOTALMINUTES] Is Null,
0,
(60/[TR_PRICING].[Form]![TOTALMINUTES])
)
You're testing to see whether it is null, not zero, so that when it is zero,
the test returns false and you go to the second condition, i.e., dividing 60
by TOTALMINUTES. Since TOTALMINUTES is zero, you naturally get a
divide-by-zero error.
If you really want things as you say, change the test clause in your IIF
statement to read:
TR_PRICING].[Form]![TOTALMINUTES] = 0
This, however, may bomb if you do, in fact, have nulls in your data. If so,
you will probably need something like this:
nz(TR_PRICING].[Form]![TOTALMINUTES]) = 0
or maybe even
Val(nz(TR_PRICING].[Form]![TOTALMINUTES])) = 0
depending on what data types you have in your source.
Pete
Ashley said:
If the totalminutes is not zero, the calculated control
(60/[totalminutes])
is fine. But when totalminutes is zero, the field display #div/0!. But I
would like it to display zero.
KARL DEWEY said:
Where are you wanting to use it as it makes no sense.
It is testing for a value and isplaying zero if no value but dividing 60
by
the value if it exist.
If you divides minutes by 60 you get hours but what do you get dividing
60
by a number of minutes?
BTW golfinray stated how it should look.
--
Build a little, test a little.
Ashley said:
This is a control field. It is unbound to a table. What should the
statement
looks like?
:
IIF statements are laid out like this:
IIF([yourtable!yourfield]is null,[what to do if true],[what to do if
false])
--
Milton Purdy
ACCESS
State of Arkansas
:
Hi,
I have below function in the control source. It returned #name?
Basically, I would like "if totalminutes is zero, then this field
is
zero,else 60/totalminutes
IIf([TR_PRICING].[Form]![TOTALMINUTES] Is
Null,0,(60/[TR_PRICING].[Form]![TOTALMINUTES]))
Where should I put this IIF function?
Thanks
