Question

Locked

Calculations in MS Access

By ian.horsey ·
Hello

I am trying to write a databse which allows me to track the amount of various gasses in oil samples. With this data I want to be able to trend the results and also perform some simple calculations on it.

I have managed to carry out the calculations ( a simple a/b calculation) using a query, but I then want to run another query which only displays the results which are greater than 10. When I run the query I get a divide by zero error, and error, followed by a stack overflow error.

I get some #Error values during the initial calculation stages - could this be the cause? I have tried implementing the NZ function but I don't seem to have got the syntax correct as it still gives me #Error in the calculation query.

Am I asking too much from Access to do this for me? Should I just stick with my spreadsheet? Any help and/or advice would be greatly appreciated.

Thanks

Ian

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Div by zero, hmmm

by Tony Hopkinson In reply to Calculations in MS Access

Could be lots of things that, though from your description it sounds like your query isn't doing what you think it's doing.

If I was guessing and that's all I can do from here, I'd suggest some of your errors are done to null values in fields. Null is a unique value in a database 0 + Null, for instance is null.

There are a few ways you can deal with that.
Force 0 into all the nuls in your data, there is also a function IsNull.
Select IsNull(SomeColumn,0) From SomeTable, will substitute 0 for any nulls in the the column.

You need to get those #err chaps out of there, either by getting them to come up with a valid value , or by cutting them out in a where clause.

HtHs

Access will do it, you've just got to give it some clues.

Related Discussions

Related Forums