-1

I can't believe in what is happening and is so simple to prove this issue. You just need to execute the code below into a ACCESS DB using DAO.

CREATE TABLE Table1(Field1 Single)

INSERT INTO Table1 (Field1) VALUES(9.99)

Then 

SELECT * FROM [Table1]

the result is

Field1 = 9,98999977111816

That is a big deal because if you insert something like 2000 rows and then sum this field, the value is starting to be far and far from the expected sum of the values.

Adding more information,

Currency have FieldSize = 15 to store, Single have FieldSize = 7 to store, I need to use Single because the storage limit is very important to me.

Solutions are very good. Speculate about is lose time. True do not deserve down votes.

And what about this issue, does anybody have the same problem? Is that a documented issue? Lets talk abou this behavior, who is with me?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Paulo Paredes
  • 3
  • 2
  • *"does anybody have the same problem?"* Yes. It's just the way floating point (Single, Double) data types work in *any* context, not just Access. *"Is that a documented issue?"* Yes: [Is floating point math broken?](http://stackoverflow.com/q/588004/2144390) – Gord Thompson Aug 08 '16 at 16:26
  • that what I´m talking about! thank you so much! i´m sorry I can´t give you up to this comment, this is a new account! but I can scream your name, Gord Thompson IS AWSOME. – Paulo Paredes Aug 08 '16 at 16:31

2 Answers2

3

What you see is a floating point error - or rather limitation.

You should always use data type Currency if you wish to perform calculations on the data and you don't need more than four decimals.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Currency uses 15 bytes, Single uses 7 bytes, I need to use Single because the storage limit is very important to me. Thank you anyway, but I have to use Single. – Paulo Paredes Aug 08 '16 at 14:37
  • 2
    You cannot have both. That's by design. And it is not an Access limitation; it is the condition for every database engine you may encounter. You could perhaps scale the values to integers, but that will require calculations for all reading and writing. Not good. – Gustav Aug 08 '16 at 15:05
  • 1
    @PauloParedes re: "I need to use Single" - No, you don't. You need to find a proper solution to your issue, and using `Single` isn't it. – Gord Thompson Aug 08 '16 at 15:45
  • I need, because I cant change a application that have 24 years, the only thing a can do is change this field type. – Paulo Paredes Aug 08 '16 at 16:06
3

Currency uses 15 bytes to store

No, it doesn't. It uses 8 bytes to store and is accurate to 15 digits to the left of the decimal point (ref: here).

Single uses 7 bytes

No, it uses 4 bytes. However, it is a floating-point representation and hence has the limitations of any floating-point data type as described here:

Is floating point math broken?

If the extra storage space required to use Currency is a real problem for you then you should take another look at the overall design of your application. For example, if you are concerned about the 2GB limit for an Access table (database file) then perhaps the Access Database Engine is no longer the right tool for the job.

Addendum:

Thanks to @HansUp for reminding us that the limit was 1GB prior to Access 2000, and the OP is indeed using Access 97. After ~20 years, maybe it's time to upgrade to a more recent version of Access.

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • allright. FieldSize = 7 in Single and FieldSize = 15 in Currency. Do you have the soluttion? because I cant change this application that have 24 years, the only thing a can do is change this field to Single to win space. – Paulo Paredes Aug 08 '16 at 16:10
  • A "solution" to conserve space that also produces wrong answers is not a "solution". Is it the 2GB size limit that you are trying to avoid? – Gord Thompson Aug 08 '16 at 16:29
  • Prior to Access 2000, the database file size limit was 1 GB. If your database is Access 97 or earlier, converting it to a more recent MDB or ACCDB format will raise the limit to 2 GB. – HansUp Aug 08 '16 at 16:38
  • 1
    @HansUp - Thanks for clearing that up. To be honest, I had forgotten about the earlier 1GB limit, and if the application in question is indeed 24 years old then that's a distinct possibility. – Gord Thompson Aug 08 '16 at 16:42
  • what??? man I think you give me the one last breath!!!! let me see this, I´m telling this because i really use access 97 – Paulo Paredes Aug 08 '16 at 16:42
  • @HansUp is AWSOME!!! Gord Thompson is AWSOME and attentive! THANK YOU BOTH!!!! Please post this awser so I can ACCEPT!!!! – Paulo Paredes Aug 08 '16 at 16:55