Error in formula (1 Viewer)

[fedupwithaccess]

Hi,

I've currently got several formulas like the one below in my query for which when dividing by 0 I get #error (which I was aware occurred). However, I though my formula would return 0 in such a case (lilke Excel would), but it hasn't

The fields which it is using to calulate the formula have number values, for which if no score is entered then the default value is 0. I would rather not leave this blank and retain the zero value (I haven't looked in to nz function any further because this if for blank fields?) so any help is gratefully appreciated.

The forumla returning #error is:

UKAdj%: IIf(IsError([UKAdjTot]/[UKTotal]),0,([UKAdjTot]/[UKTotal]))


Cheers,
Iain

 

[Mile-O]

I would create a custom function and sling it into a module:

Public Function DivError(ByVal top As ????, ByVal bottom As ????) As Boolean
    On Error Goto Err_DivError
    Dim temp As ????
    temp = top / bottom
Exit_DivError:
    Exit Function
Err_DivError:
    DivError = True
End Function

You would replace ???? by whatever data type was relevant to the numbers you are dividing: interger, long, single, or double.

Then, in your query:

=If(DivError([TopField], [BottomField]),0,[UKAdjTot]/[UKTotal])

I am a bit concerned, however, that you have a field called UKTotal. I think your table structure may be a little suspect.

 

[fedupwithaccess]

error in formula

Thanks SJ. Like your suggestion and would have implemented it, but since realised that my value for UKAdjTot would always be 0 or a true value.

Basically the answer can only ever be returned correct or as 0 (#error)

Working on this basis I changed the formula to:

UKAdj%: IIf([UKAdjTot]=0,0,[UKAdjTot]/[UKTotal])

which all works fine.

Thanks for the input though.

 

[Mile-O]

Out of interest, what is the Total field all about? It's not a field summing the values in a series of other fields, is it?

 

[fedupwithaccess]

No it's not summing other fields just yet and got that one covered thanks.

It relates to bonuses for staff (which is currently on a very slow 35MB spreadsheet) for which they are graded against various measures and then their scores adjusted/weighted according to the system speeds, volume of work etc.

Calculations are made on each measure from their possible score against their adjusted score.

I'm sure it could probably be condensed better than what I have done, but whilst it's working I'll take it and continue to develop it as I go along whilst learning further commands, functions and code within access.

I'm finding some of the vb code much easier than putting a formula into a newly created expression on a query.

Cheers.

 

[Mile-O]

I'm sure it could probably be condensed better than what I have done, but whilst it's working I'll take it and continue to develop it as I go along whilst learning further commands, functions and code within access.

I understand. But just be aware that in effective database design, you do not store the results of calculations. These are always done in a query or on the form. Wouldn't do any harm to learn about normalisation in addition to "further commands, functions, and code within access" as this is the foundation of good design, without which you'll be unnecessarily falling over yourself to find functions and whatnot to solve problems that are easily done with a solid base.

 

[fedupwithaccess]

Thanks for the pointers.

Luckily all my calulcated formulas are on my queries then (which my reports run from and the user ultimately sees), but looks like normalisation is the next chapter for me.