Division by Zero, arrrgh!

[thenoisydrum]

Hello everybody,
We've all seen this error before and normally when I come across it I can see where the problem lies and I can fix it.
This time however I can't and I'm starting to get a little bit, shall we say, annoyed :banghead:

I am running a very simple query that divides profit over turnover to show the margin. I have many queries throughout my database that do this, here is my little formula;

Margin: Round(([profit]/[Turnover])*100,2)

Now, this runs just fine but if I put in a criteria of <10 I get the division by zero error. The reason I am confused is that there are no zeros or error values or even negative values in either of the profit or turnover columns?
Also I have an almost identical query in another database that has a <5 criteria in it and it works a peach.

Where the bejesus am I going wrong?
I have searched and searched online for a solution but all I am getting is the usual definition of the division by zero error.

Any help would be appreciated

 

[pr2-eugin]

thenoisydrum.. The error more indicates that Turnover for some data is ZERO.. So I think it is best to trap it, before performing the caluclation.. Maybe use a function..

Margin: getMargin([profit], [Turnover])

Where getMargin is a user defined function..

Public Function getMargin(profitVar As Double, turnVar As Double) As Double
    If turnVar <> 0 Then
        getMargin = Round((profitVar/turnVar)*100,2)
    Else
        getMargin = 0
    End If
End Function

 

[thenoisydrum]

Thanks Paul.
I understand what you are saying but there are no zero turnover or profit values. In my "base" query I have 72 records. The turnover figures are all positives and the profit values are calculated by subtracting the cost from the turnover.
All of the profit values are above zero too.
It is the second query that contains the margin calculation by the way.
Just letting you know how I have structured it. I need to calculate the profit in the first place and sum all the values (turnover, cost and profit) by stock code in the first query and then in the second I calculate the margin and apply the <10 criteria.

 

[pr2-eugin]

Hmmm.. So I think there is a possibility that the First query returns some zeros? Apply the <10 criteria in the First Query.. and see if the result you get is right..

 

[thenoisydrum]

Hmmm.. So I think there is a possibility that the First query returns some zeros? Apply the <10 criteria in the First Query.. and see if the result you get is right..

Well Paul I have rebuilt the query from scratch and it works.
I can do it all in one stage rather than two and for the life of me, I cannot see what the problem was but thanks for your help anyway

 

[pr2-eugin]

Glad you have it sorted.. I think it was the filtering in the Second query that might have been the problem.. Since now you have integrated them into one, the Criteria is first applied before performing the calculation..

Good luck !!