Rounding of number not desired

[Bill_n_miami]

3rd decimal place rounding of number not desired

Hi all and thank you to those that answer questions to help those of us less skilled with Access.

I am trying to make two calculated fields come out looking the same in a query. When I hover the mouse cursor over them one has a value 48.9932 and the other has a value of 48.9974. When the mouse cursor is moved away they round off and display as currency as $48.99 and $49.00 respectively.

In the calculated formula for each of them there are some 3 decimal place multipliers used. Could this be causing the problem. I must have the 3 digit multipliers in the formula.

I have tried changing number types, decimal places, etc in the underlying table, but nothing seems to prevent them from displaying this way.

In the calculated formula for each of them there are some 3 decimal place multipliers used.

Is there a way to make them come out even by ignoring the 3rd and 4th decimal place?

Thanks in advance.

 

[John Big Booty]

It sounds as if your Field Data Type is set as Currency. Set it to Number with a field size of Single.

 

[Bill_n_miami]

Thanks JBB. I tried as you suggested but the numbers still come up with the discrepancy. It is a calculated field that only has a value in it when I run the query or report and they are both set to
Field Data Type: Number,
Field Size: Single,
Format: Currency,
Decimal Places: 2.

 

[John Big Booty]

Remove Currency from the Format leave that blank, and set the Decimal places to the desired length

 

[Bill_n_miami]

JBB that didn't make any difference either. Since they are calculated fields would any of the fields they are derived from impact this?

 

[John Big Booty]

Shouldn't do. Are you able to post a copy of your DB ('03 version if possible)?

 

[vbaInet]

The OP mentions that these are calculated fields so it sounds to me that you have set a Format property on the field which is causing it to round when the cursor is not in the field.

If you want it to remain as a rounded figure you need to surround the calculation in the Round() function, like this:

Round(Calculation here, 2)

where 2 is for 2 dp.

 

[Bill_n_miami]

Shouldn't do. Are you able to post a copy of your DB ('03 version if possible)?

It looks like the database may be too large. It is 20mb and the upload window looks to be 2mb limit.

 

[Bill_n_miami]

The OP mentions that these are calculated fields so it sounds to me that you have set a Format property on the field which is causing it to round when the cursor is not in the field.

If you want it to remain as a rounded figure you need to surround the calculation in the Round() function, like this:

Round(Calculation here, 2)

where 2 is for 2 dp.

I am not discounting your response I just do not understand it. I a ma beginner with Access having cut my teeth on Paradox DB in 1986, I transitioned to Access when it became available but have never understood SQL or VB languages. I use the programs as they are without many modifications.

 

[vbaInet]

Ok, I'm not clear on your objectives either. You simply don't want it to round the value right?

 

[John Big Booty]

Try doing a compact and repair on your DB, then put it in a zip file and try uploading that.

 

[Bill_n_miami]

Ok, I'm not clear on your objectives either. You simply don't want it to round the value right?

Right. I want it to drop the 3rd and 4th decimal values and leave both calculated fields values at 48.99.

When I run the query the Curr Due value is $48.9932 when I click in it and $48.99 when I click out of it.

Total Due value is $48.9974 when I click in it and $49.00 when I click out of it.

These are the two calculated fields in the query.

Curr Due: [Water]+[RTS Water]+[Sewer]+[RTS Sewer]+[Utility Tax]+[Admin Chg]

Total Due: [Bal]+[Curr Due]

Some of the values like [Utility Tax] extend out to 6 decimal places. Maybe they are adding up to a different amount because of all the decimal places than the second formula.

See the attached jpeg for how this looks on my report.

 

[vbaInet]

Your post title contradicts what you want and what I just asked you isn't what you were after. But, not to worry, I understand what you're trying to achieve. You actually want to Round the values by 2 decimal places and keep it that way.

Here:

Curr Due: Round([Water]+[RTS Water]+[Sewer]+[RTS Sewer]+[Utility Tax]+[Admin Chg], 2)

Total Due: Round([Bal]+[Curr Due], 2)

Note how the Round() function was used as explained in my other post.

 

[Bill_n_miami]

Your post title contradicts what you want and what I just asked you isn't what you were after. But, not to worry, I understand what you're trying to achieve. You actually want to Round the values by 2 decimal places and keep it that way.

Here:

Curr Due: Round([Water]+[RTS Water]+[Sewer]+[RTS Sewer]+[Utility Tax]+[Admin Chg], 2)

Total Due: Round([Bal]+[Curr Due], 2)

Note how the Round() function was used as explained in my other post.

It worked like a charm and I do see how the Round function was applied to the formula so I have learned something in addition to having the problem resolved. Thank you!

 

[vbaInet]

Glad we could help!