Number errors in MsAccess

[unusedhandle]

I have a problem with an access database which I made to reconcile bank accounts.

The data type I use is a double number. The problem is that whenever I enter any data in this field that is not a whole number, Access 'changes' the decimal places to some random looking 9 point fraction.

I realise I'm probably using the wrong number type, but I'm not really sure how it works - regardless, I don't understand why Access can't use the number 450.32, but insists it is 450.3167436 or similar!!

The reason it bothers me is that it actually produces small errors in balances when a lot of data is entered. I can't then tell if I've made an error, or Access has, without ploughing through the statements.

Any help or better any explanation greatly appreciated.

 

[Dreamboat]

Go into the design of the table. Enter the data type as a number. Down at the bottom, hit the dropdown and choose a currency. This is probably most appropriate for you purposes.
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[meagain]

Sorry about the handle, it gets a bit tiresome after 5 attempts.

Tried that, set currency, fixed format with 2 decimal places, but Access still uses 4 decimal places.

I guess Bill has his reasons . . .

Thanks for the advice, it has halved the problem!!

 

[Dreamboat]

Where does it use 4? In a query or report or form?
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[Zelandakh]

Silly question, but instead of coming in as a Visitor and having problems finding a handle, why don't you register on the site - much easier.

 

[SimonDavis]

The 4 place numbers are in the table. That data goes through various filters, and is displayed in various forms, some of which also re-filter. The discrepancies are in every stage.

It it totally inconsistent though - I have several thousand entries, some of which are 2 decimals (correct). After your suggestion to change the number type to currency, it truncated the rougue numbers from 8 decimals, but to 4.

It does seem to be accepting the numbers correctly now - I have made several entries since changing the format as you suggested, and all is OK. It has not corrected the ones that were wrong before though. As I haven't yet worked out why it did it, I can't say for sure that it won't do it again! Maybe it's something to do with the weather.

Confusing. I still can see no reason why this software decided the number I entered was not correct, and changed it.

Thanks for your time.

 

[SimonDavis]

Just a suplementary.

The data always diplays with 2 decimals, as I have formatted it like that. It is only hen you click on the number that you see the actual number Access is using. Probably obvious.

 

[Dreamboat]

Not obvious, Simon. I suspect the weather too, but it could be that it's not the weekend. Perhaps it will work tomorrow or Sunday?

Perhaps you need to round your figures. Create a query that will do this for you and then turn it into a make-table query, create a new table and trash the old. Rename the new to the old and borrow something blue and that might do it!

I'm so terrible with Access (relatively speaking to my capabilities with the rest of the Office suite) that I'm not really sure of an appropriate way to do this, so if it were me, I'd probably take the table out to Excel, run the calcs, edit-paste special as values to force two decimals and no "underlying" values and pull it back into Access again.

Then again, Simon, perhaps you ought to get thyself where you belong; there are many Access-related forums here. Look for MS Access, not Office. But they only work on Friday's, so you'd better hurry!

(that was fun)
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com