Need help stopping MS Access 2003 is rounding up some of my numbers automatically

[jlathem]

Hi,

I hope someone can shed some insight on this issue for me.

Whenever I enter (directly into the table’s field) a number over 11111110 something rounds the value up.

When I enter 11111110 I get 11111111, if I enter 12345678 I get 12345680

My Table Field’s Data Type set to Number, Field Size set to Single, decimal Places set to 0.

The Super Moderator Bob Larson has been very gracious and helped me work through several issues I had and when he tests the above configuration in his Access it works for him.

I am just at a loss for why this is happening.

Can anyone help?

Thanks,
James

 

[Galaxiom]

Sounds odd. Is there anything entered in the Format Property of the field?

If you are only using whole numbers use Long Integer as the field type.

 

[boblarson]

If you are only using whole numbers use Long Integer as the field type.

They can't because the numbers are too large for Long Integers.

 

[jlathem]

Sounds odd. Is there anything entered in the Format Property of the field?

If you are only using whole numbers use Long Integer as the field type.

Nothing in the Format field.

And Bob is correct, I need to be able to hold a number 13 characters long.

Thanks for any help you can give.

James

 

[MarkK]

OP is talking about a single, and I believe a single can store 7 significant digits. If you supply more they are truncated.
Use Double to get 15 significant digits.

 

[boblarson]

OP is talking about a single, and I believe a single can store 7 significant digits. If you supply more they are truncated.
Use Double to get 15 significant digits.

That is DECIMAL precision, not the numbers BEFORE the decimal. The OP doesn't want any decimal places at all.


I just ran another test and setting it as DECIMAL with the SCALE to 0, worked (and make sure to not use quotes in the format of the control).

 

[jlathem]

Got it... Thanks Bob!

James

 

[jlathem]

Hey we have a winner!!!

The Double works! Thank you so much Guys.

FYI though, for some reason the Single, at least for me, didn’t truncate but just rounded up. Go figure!

Thanks again!

James

 

[boblarson]

Personally, I would go with Decimal as Single and Double are fraught with rounding issues and you may find yourself with it happening again. But if not, good.

 

[jlathem]

Do you mean set the Decimal Places to 1 and not to 0?

 

[boblarson]

Do you mean set the Decimal Places to 1 and not to 0?

No, I mean selecting the datatype of DECIMAL (instead of NUMBER) and there is a property for DECIMAL called SCALE and you set it to ZERO (0).

 

[jlathem]

Thanks for getting back to me.

I checked the table’s data type in Access 2003 and don’t see an option for DECIMAL. Would this be an option in a later version like 2007 or 2010?

Jms

 

[boblarson]

Thanks for getting back to me.

I checked the table’s data type in Access 2003 and don’t see an option for DECIMAL. Would this be an option in a later version like 2007 or 2010?

Jms

No, it's there in 2003 as well. But I did mislead you to where it was - Take a look at the screenshot:

 

[jlathem]

Ah, I do see it now.

In order to allow the field to store a number 13 character long will I need to set the attributes (Precision, Scale, or anything else) to any specific value?

Jms

 

[boblarson]

Just leave Precision at 18 and Scale to 0.

 

[stopher]

That is DECIMAL precision, not the numbers BEFORE the decimal. The OP doesn't want any decimal places at all.


I just ran another test and setting it as DECIMAL with the SCALE to 0, worked (and make sure to not use quotes in the format of the control).

Lagbolt is right. The 7 significant digits means that Single only records the first seven digits (reading from the left). The position of the decimal point is not really an issue since it is dealt with by a multiplier.

So 1234567890 will be stored as:
1.234568 x 10^9 (Single)
1234567890 (double)

1234567890123 will be stored as
1.234568 x 10^12 (single)
1234567890123 (double)

12345678901234567890 will be stored as
1234568 x 10^19 (single)
123456789012346 x 10^19 (double)

123.456789 will be stored as:
123.4568 (single)
123.456789 (double)

So double will do the job fine.

As for decimal, I only see it relevant where I want to fix the level of precision. I don't see that that is the target here. Also note the issues with Decimal here. There's also extra overhead using decimal.

Chris

 

[jlathem]

Thanks Chris