calculated control

[loneranger27]

Hi,

I have a text box with the following in the control source
=round([text29]/count([itemcount])*5),1)

[itemcount] is the number of boxes of that item and there are 5 items in each box.

the problem I am having is that sometimes the count field will should be null so that nothing is counted but when the field is null i get div by 0 error is there a way to tell it that if [itemcount]=0 then itemcount=1 so i dont get a div by 0 error or is there another way.

Any help is appreciated.
Thanks

 

[THWatson]

Maybe I'm not seeing this correctly, but isn't there something wrong with the formula? Specifically with the "count([itemcount])" part?

Since Count is a function, won't counting the # of [itemcount] fields and multiplying by 5 always yield 5?

I am assuming that the [itemcount] field is numerical. So wouldn't it be "([itemcount]*5)"?

Or maybe I'm just not understanding.

That still doesn't solve the divide by zero problem, but I thought I should raise this anyway.

Tom

 

[THWatson]

Further to my other post, I don't know whether or not this gives you what you want. But when I first read your post I thought about some IIf formula that would give a 1 if the [itemcount] field was 0.

This will do that...
=round([text29]/(IIf([itemcount]=0,1,[itemcount]*5)),1)

The only difficulty is that, when the [itemcount] field is 0, the 1 yields the same # in the formula field as exists in the [text29] field.

However, maybe this points to something you can use. Like replacing the 1 with "out of stock" or something.

Tom

 

[THWatson]

If you would sooner not have the [text29] number show up in the calculated text box, and would sooner always have a 1 show up there if the [itemcount] field is 0, then this would do that...

=round([text29]/(IIf([itemcount]=0,[text29],[itemcount]*5)),1)

Tom