getting error result when field is 0

[chriswrcg]

In my DB I have to create a query that subtracts one number with another. Sometimes those numbers will be the same so the result should be 0. Access however is returning a very long number as an error. How can I set the field to allow 0 as a result?



Here is my DB. Query in question is qryProductiveTime

[alansidman]

change your calculated field in the query to: TProductiveTime: Round([reportedtime]-[sumofeexceptionduration],0)

[chriswrcg]

Thank you sir. That did it.

[isladogs]

It's not an error. The calculated value indicates E-14 which is a very small number with 13 zeroes after the decimal point then a value in the 14th digit.
It occurs because of the many calculations done prior to that or because of how you defined the underlying number fields.
For example if you use Double numbers you may get what is called floating point errors.

Anyway to fix it, change your calculated expression to TProductiveTime: Int([reportedtime]-[sumofeexceptionduration])
Using Int ensures the result is an integer and gives zero correctly in this case

As a side issue, it is a bad idea to start object names with a number as you have for many queries.
It may well cause you unexplained errors at some point