delinquent decimal (1 Viewer)

[Matthew Snook]

to all:
My database includes data imported from old Excel files. Since the initial transfer, all data entry has been through Access forms and queries.

On one field, [Individual_Egg_Weight], the imported data were in the form 0.00 where all values were less than one and weights were recorded to the nearest hundredth of a gram (these are salmon eggs).

My Access table shows all data, both imported and directly entered, in that same form: '0.00' and all data looks the same. However, when summary operations are performed in a query, such as 'Sum' or 'Avg', the aggregate function is in error. All of the old data is summed properly, but all of the new direct data is treated as if there were no decimal! So instead of an egg weighing 0.32 grams, they are treated as if the number is 32 grams. Keep in mind that if you look at the number, or even copy and paste it over into Excel, it comes out as '0.32' as it should. But an aggregate query will add '0.32' and '0.34' to get '66' and will average them to '33' instead of '0.33'

What's up with that? The table defines this field as number type 'Double' with decimal set to 'Auto' and an input mask which reads "0."00 - When we enter data, the '0.' is put in place as soon as we enter the '32' so that the input looks like '0.32' when we finally hit the enter button. The number is displayed as '0.32' and copies directly into Excel as '0.32', but cannot be summed properly.

What am I missing?

Thanks,

Matt

 

[The_Doc_Man]

That is because the format include the 0. as a quoted string. It really isn't there in the internal data field, but it is there in any normally displayed field using that format.

If it isn't too much trouble, try to export one of these tables to a comma-delimited text file and see what is written to the file. If you see 2-digit numbers without the 0, you know what is being stored. Storing the data with that quoted "0." means you never have to key the decimal point, which is why someone would have made that choice. Saves keystrokes. But if you didn't key the decimal point, you probably didn't store it, either.