Help! VBA Code help needed

[RJL1]

I have a report that lists avg cartons pulled per hour. I would like to highlite each recors where the avg falls under 250. I try using a box around the detail section and set the backstyle to 0 or 1 depending on the number here is the sample code i'm using

If Me.avg_ctns < 250 Then
Me.highlt.Backstyle = 0
Else
Me.highlt.Backstyle = 1
End If

This works great until one of the records has zeros as the orders pulled or hours worked and the the avg gets calculated as divided by zero which in turn errors out the report. Is there anyway I can avoid having divided by zero as my results.

Thanks
Ricardo

 

[PaulBricker]

Wherever you do the calculations, wrap the expression in an IIf statement

IIf([YourField]=0,0,[Field]/[YourField])

That should return 0 for any records where YourField is 0

Paul

 

[MichaelRed]

but consider the implication. this set a SEEMINGLY valid value into a place where NO value is valid.

I would suggest the displayed value be set to an empty string or, perhaps &quot;N/A&quot;, but NOT a numerical value.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PaulBricker]

I don't know that I would agree or not. If the cartons is 0 or the Hours is 0 then the Cartons/Hour can be 0. It may not be mathmatically correct, but logically it's OK.

Paul

 

[RJL1]

Thanks:

I'm using =IIf([Total CTNS]>0,[Total CTNS]/[Total Orders]) and leaving the value blank if Total CTNS is less than zero. I do agree that mathmatically is probably not right but it works for what I'm doing.

Thanks

Ricardo

 

[MichaelRed]

having been soundly rebuffed, i hesitate to jump back in, BUT

the expression should -if anything- check the denominator, NOT the numerator. division INTO zero is not (normally) a problem, while division BY zero will ALWAYS generate an error.

as for the math, it may not be important, then again aggregate functions will reflect the value entered -and thus not be all correct.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PaulBricker]

You don't need to be rebuffed, you're already polished enough. What you say is true. It's only division by 0 that's an issue and the aggregates won't function if the 0 isn't correct. But then x = x + 1 works and you never learned that in algebra.

 

[MichaelRed]

oh, but i DID learn than (and the secret little divide by Zero trick which allows the 'proof' to seem to work)! one of the reasons i get so picke about it???


MichaelRed
m.red@att.net

Searching for employment in all the wrong places