Expression Rounding

[Scorpio11]

I have created a query in access that has a calculated cell. In the cell, the below formula can be found:

Cost: IIf([Type]="Purchase",[Amount]),[COS])

The information that this query pulls is information that I type into a cell, it does not pull any information that is a result of an expression. The source table has the number fields that are formatted as Single so that i can add in the 2 decimals for the cents of the transaction.

My question is this. Why does this expression add arbitrary decimals to my result when the information it pulls from does not have those numbers in.

EQ: The number is it pulling in is 411.66. The result is 411.660003662109.

I have tried a round expression, but the decimals remain and nothing changes. This is causing a problem as i am grouping similar transactions into a single line and excluding the lines that have a nil using I then have a <>0 criteria to exclude the Nil Balances.

The decimals are causing lines that should be Zero and excluded to be included in the list. Changing the format to Standard with 2 decimals does not help either (not that i thought it would, but i was as the end of the perverbial rope)

Is there a reason why Access does this, is there an expression i can use or perhaps a format i can use in the source table to prevent the expression for adding these phantom numbers.

[jgelpi16]

Did you try to limit the number of decimal places in the properties of that field in your query? Right-click on the field and go to properties. In Access 2007+ you see a bar to the right pop up. Click on the "Format" tab and format your field to numeric of some kind and then set your number of decimal places to 2.

[Scorpio11]

Yip - i did that. The result was 0.00, however, the line itself was still there.

[SteveF]

I have created a query in access that has a calculated cell. In the cell, the below formula can be found:

Cost: IIf([Type]="Purchase",[Amount]),[COS])

The information that this query pulls is information that I type into a cell, it does not pull any information that is a result of an expression. The source table has the number fields that are formatted as Single so that i can add in the 2 decimals for the cents of the transaction.

My question is this. Why does this expression add arbitrary decimals to my result when the information it pulls from does not have those numbers in.

EQ: The number is it pulling in is 411.66. The result is 411.660003662109.

I have tried a round expression, but the decimals remain and nothing changes. This is causing a problem as i am grouping similar transactions into a single line and excluding the lines that have a nil using I then have a <>0 criteria to exclude the Nil Balances.

The decimals are causing lines that should be Zero and excluded to be included in the list. Changing the format to Standard with 2 decimals does not help either (not that i thought it would, but i was as the end of the perverbial rope)

Is there a reason why Access does this, is there an expression i can use or perhaps a format i can use in the source table to prevent the expression for adding these phantom numbers.

What data type are you using to store your amounts? If you're using Single or Double, you may be running into rounding errors which cause something that should be zero to have a very small non-zero value.

Instead of Single or Double, try using Currency; it doesn't suffer from rounding errors.

Steve

[Scorpio11]

I was using Single. I just switched to Double as a test. and it solved my problem.

The currency is the format. My problem was the field size.

Thanks for the tip

[jgelpi16]

Glad you figured it out, glad to help!

[SteveF]

I was using Single. I just switched to Double as a test. and it solved my problem.

The currency is the format. My problem was the field size.

Thanks for the tip

Double may have solved the problem for now, but that's only put off the inevitable rounding errors for some future time.

For calculations where rounding error is not acceptable, you MUST use a Currency, Integer or Long data type: both Single and Double are subject to rounding errors, it's an inherent problem with binary-based floating-point number types. If you need numbers to the right of the decimal, then Currency is the only option free of rounding errors.

Steve