Confused by data types and precision (1 Viewer)

[GK in the UK]

A long time ago as a total novice I made my price fields as Currency which gives me prices to 4 decimal places of one UK pound.

How can I increase the decimal places stored ? I need up to 5 for prices, and possibly more for another function, with total precision.

Looking at the table design, I see I can define a Long Integer and specify the number of places. But isn't a long integer 4 bytes ? Don't think that will do it.

Can I change Currency to some other 8-byte type but with different decimals ?

I have a solution in my head which is probably me over-thinking it as usual. Multiply the user-keyed value by 10 (or more) and save it to the table. I suspect someone has a better solution.

 

[arnelgp]

can you not define the Number of decimal places while
in design view of your table.

 

[The_Doc_Man]

If you use DOUBLE, you get approximately 15 reliable digits of mantissa. That means you could have 9 digits to the left of the decimal and six to the right, giving you numbers up to just less than 1 BILLION (USA definition of billion). Do you have a billion-pound total business flow?

Does British currency have precision to 5 decimal places or is that because of fraction-of-a-penny considerations for bulk purchase discounts?

 

[isladogs]

Or use SINGLE which typically gives 6 or 7 d.p .... IIRC

 

[The_Doc_Man]

Colin, SINGLE probably won't work so well in the case as described. That isn't 6 or 7 decimal places - it is 6 or 7 digits of mantissa, which isn't "decimal places."

I have actually run across a data type of QUAD integer, but (a) it only appears on 64-it machines and (b) as I recall, isn't THAT tractable.

 

[GK in the UK]

Billion pound turnover, ha ha I wish

It's not the integer part that's the problem. You'd think it absurd that I have invoices priced to one-thousandth of one penny but that's how the utility companies price my electricity and gas. I've been having to tweak my input because I can enter prices to "only" one hundredth of one penny (Currency data type with 4 d.p.). Sometimes I can't get my invoice line to agree with the utility bill no matter how I tweak the price or the quantity. Yeah, I'm obsessive.

Another situation is conversion of quantities. It's in my head that I can convert a UK pint to litres by multiplying by 0.56825 so that's another 5 d.p. situation and I'd like to have some spare capacity.

Now, you say, DOUBLE, I get approximately 15 reliable digits of mantissa. Is that really true ? I'm only asking because we've seen a few posts where people are getting unexpected results with floating point, and Double is floating point, I think. Can I be SURE that I will get a good reliable repeatable result with this type ? I'm sort of very cautious about it, trying to avoid floating but maybe it's misplaced concern.

 

[isladogs]

Hi Doc,
Yes I realise that which is why I said 'typically'
I was going to go back and clarify it but you got there first

The advantage of using SINGLE instead of DOUBLE is that it requires less disk space
Single uses 4 bytes; double 8 bytes

For info, the latitude & longitude data below is all SINGLE - in some cases fewer d.p. due to rounding

 

[gemma-the-husky]

Don't mix double and single, as you will get strange results. As field size is unlikely to be an issue, I would use doubles. I only ever use longs for integer values as well.

 

[isladogs]

I fully agree you shouldn't use a mixture of single & double. Similarly I wouldn't recommend Decimal

There are two reasons I use single for several fields in my postal address database:
1. Doing so took a whopping 210 MB off the database size - even so its 1.65 GB. (Its read only so no risk of ever reaching 2 GB)
2. It provides sufficient precision for that purpose. Double would be overkill

 

[GK in the UK]

OK thanks all. Looks like I'll go with Double. An early design decision that wasn't the best but it can be fixed.

 

[Pat Hartman]

Just FYI, Currency (the data type) is a scaled integer. That means that 10 cents (.10) is stored as 1000. So you could use integer and scale it internally yourself which is a PITA or you could use double as the others have suggested. However, you will get floating point errors when you use either double or single so be careful because columns of figures might not add up. And if a user might ever take a calculator and add the values, he would see that you're off by .00003 or whatever. Read "When Access Math doesn't add up" at www.fmsinc.com for an explanation. For anyone who has ever used SAP, this was a quirk. Since SAP is supposed to be everything to everybody and they didn't want to do error trapping, they stored numeric values as strings and we had to deal with that.