How do prevent getting an error message in a field when the field contains an expression that has a division by zero. The expression is " [parts]/hours]/8)" Whenever "hours" is zero, I get the error message "#Error" in the field. Someone recomended an error check but I am not familiar with visual basics. I cannot use the NZ or tne IsNul function because the default value in the field is not blank its zero. Pleeeeze some one help me,any help will be greatly appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
need to do division by zero and get zero not #Error 2
- Thread starter Keving74
- Start date
-
2
- #2
- Thread starter
- #3
MichaelRed
Programmer
Hmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm,
BUT! This is the obverse of the normal (computer) math. The generically appropiate is to either return the largest value the Processor can generate for the data type, or NULL.
Returning Zero just perpetuates the confusion!
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
BUT! This is the obverse of the normal (computer) math. The generically appropiate is to either return the largest value the Processor can generate for the data type, or NULL.
Returning Zero just perpetuates the confusion!
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
- Thread starter
- #5
Kevin,
If I may step in, I'll try to explain without waxing techno-theoretical. First, where is this field that contains this expression and what is it's purpose? Is it a report result field?
Most importantly, are you doing any summary calculations on it? This is where the Null result (you can take my example and replace '0' with Null to do this) is key. If it's just a display issue, and if zero works for you, then leave it as is. However, if there are caluclations, then it's up to you to decide: Is the true value of the result zero, or is it simply not applicable since there were no hours, and this result will poison any subsequent calcs on this field. If it's Null, then summary calcs, such as Avg & Count, will ignore it, which is mathematically the right thing to do (I can hear Wilford Brimley saying that!).
As Michael implied, in a somewhat theoretical way, is that if you divide by zero, the result is simply 'not applicable' and should be excluded from any further summary calculations--ie the true mathematical result is not zero. Again, if you just want to see a zero instead of blank, leave it, else replace with null--not the highest value the processor can create since this will *really* increase the confusion.
--Jim
If I may step in, I'll try to explain without waxing techno-theoretical. First, where is this field that contains this expression and what is it's purpose? Is it a report result field?
Most importantly, are you doing any summary calculations on it? This is where the Null result (you can take my example and replace '0' with Null to do this) is key. If it's just a display issue, and if zero works for you, then leave it as is. However, if there are caluclations, then it's up to you to decide: Is the true value of the result zero, or is it simply not applicable since there were no hours, and this result will poison any subsequent calcs on this field. If it's Null, then summary calcs, such as Avg & Count, will ignore it, which is mathematically the right thing to do (I can hear Wilford Brimley saying that!).
As Michael implied, in a somewhat theoretical way, is that if you divide by zero, the result is simply 'not applicable' and should be excluded from any further summary calculations--ie the true mathematical result is not zero. Again, if you just want to see a zero instead of blank, leave it, else replace with null--not the highest value the processor can create since this will *really* increase the confusion.
--Jim
- Thread starter
- #7
Jim,
The field is located in a form and also in a report. Calculations are done in a query and then displayed in a field on a form generated by this query (that was an experiment to try get rid of the #Error but did not work, the calculations were previously done in the field on the form)What I have written in the control source is, iif([hours]=0,0,[PPH])where PPH (Parts Per Hour)is the name of the calculated field in the query. This worked well and I have used the iif statement in other fields. On the report I did some "sum" and got some error but my idea is to do a iif(sumof([hours])"=0,0,sumof([PPMH])and that should take care of that,hopefully.
Thank you guys so much
Kevin
The field is located in a form and also in a report. Calculations are done in a query and then displayed in a field on a form generated by this query (that was an experiment to try get rid of the #Error but did not work, the calculations were previously done in the field on the form)What I have written in the control source is, iif([hours]=0,0,[PPH])where PPH (Parts Per Hour)is the name of the calculated field in the query. This worked well and I have used the iif statement in other fields. On the report I did some "sum" and got some error but my idea is to do a iif(sumof([hours])"=0,0,sumof([PPMH])and that should take care of that,hopefully.
Thank you guys so much
Kevin
- Status
