Enter zero instead of #error

[ardagh]

I have a database which is used by operators to input manufacturing data i.e. LotNo, Customer, SalesOrderNo, PartNo, LineNo, ActualQty, Length, ScrapMat, (MatStdCost), StdLineSpeed, SetupTime, RunTime, LostTime and weightperpiece.

We then use this data to produce information like
Standard Run Time: (([ActualQty]*[Lenght])/[StdLineSpeed])/60
Machine Efficiency: ([Standard Run time]/[Runtime])

these work fine if values are inserted. The problem is that when a value is zero it returns a #error value in the field. This is probably caused by dividing by zero which can happen.

A test may occur where there is zero StdLineSpeed etc.

Is there any way I can bet access to return zero in these cases instead of the #error because I wish to do further calculations on totals and averages and I cannot if even one #error occurs.

Any suggestions or help would be appreciated.

Regards
Niall

 

[KenReay]

Standard Run Time: IIF([Lenght]=0,0,(([ActualQty]*[Lenght])/[StdLineSpeed])/60)
Machine Efficiency: IIF([RunTime[=0,0,([Standard Run time]/[Runtime]))




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Or safer still:

Standard Run Time: IIF(Nz([Lenght],0)=0,0,(([ActualQty]*[Lenght])/[StdLineSpeed])/60)
Machine Efficiency: IIF(Nz([RunTime],0)=0,0,([Standard Run time]/[Runtime]))


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

More safer, replace this:
IIF(Nz([Lenght],0)=0
By this:
IIF(Nz([StdLineSpeed],0)=0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

Isn't that what I said?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi Again

No I see the divisor is [StdLineSpeed]), so you are right PHV

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ardagh]

Thank very much

It seems to be working fine - returning a value of 0.

I used the Nz function as you suggested.

Again thank you