Web Development

Ask something
Question Locked

Calculations in MS Access

0 helpful
2 replies

by ian.horsey

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

2 replies

by tony hopkinson

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.

This discussion is closed to new replies.