dividing by zero

[zuglchaim]

i have a query
[Total]/[times paying]
this will give me how much i should churge him every period of time
the best way of writing it is like this
[total]-[paid]/[times paying]-[times paid]
the problem is when finelly he paid everything then
you'll be dividing by zero, and thats returns error
how could i avoid this problem
i gess it should be something like this iif([times paying]-[times paid]),0,?????
i am very new to the sql statments

 

[danvlas]

IIf(Nz(DivideBy,0) = 0, 0, WhatToDivide/DivideBy)

where DivideBy is the expression returning the value you want to divide by, WhatToDivide the expression returning the value you want to divide
Something like:
IIf([times paying]=0,0,[paid]/[times paying])

But... I think you have a mistake in your expression:
[total]-[paid]/[times paying]-[times paid]
will substract the [times paid] and the result of [paid]/[times paying] from the total.
I think your expression should be:
([total]-[paid])/([times paying]-[times paid])

In this case:

IIf([times paying]=[times paid],0,([total]-[paid])/([times paying] - [times paid]))

HTH

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[zuglchaim]

Format(
Sum(([total1].[total]-[payment_history].[totalamount])/([CountPayingTimes].[CountPayingTimes]-[payment_history].[totalpayment#])),"$#,##0.00&quot

here is my actual fields could you do the same code here please

 

[danvlas]

Something like:

Format(Sum(iif([CountPayingTimes].[CountPayingTimes]=[payment_history].[totalpayment#],0,([total1].[total]-[payment_history].[totalamount])/([CountPayingTimes].[CountPayingTimes]-[payment_history].[totalpayment#]))),"$#,##0.00&quot


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[zuglchaim]

thank you very much

 

[zuglchaim]

thankyou