Solved Which numeric format to use? (1 Viewer)

[Gasman]

Hi all,
I discovered a problem with my Diabetes DB.
I use a factor to multiply by the set value per 100g,100ml or Each.

However whan the value is small like 0.1g per 100g, I am getting zero.
I initially had the number as Single and 2 dec places.
I then tried Currency, but I do not want the £ showing.
Currently I am on Double, but if I enter 0.1 into the table, it just changes it to 0?

So what numeric format should I be using please?

Now changed to

 

[cheekybuddha]

Use Currency.

You can remove the pound sign in the format (in the table options)

 

[Gasman]

That appears to have fixed it.

Thank you David.

 

[cheekybuddha]

 

[gemma-the-husky]

Using double (or single) shouldn't change 0.1 to zero..

 

[Pat Hartman]

However whan the value is small like 0.1g per 100g, I am getting zero.
I initially had the number as Single and 2 dec places.
I then tried Currency, but I do not want the £ showing.
Currently I am on Double, but if I enter 0.1 into the table, it just changes it to 0?

So what numeric format should I be using please?

Setting the decimal places to 2 only alters the display. It does NOT alter what is stored. Using the Currency data type which is a scaled integer, eliminates the floating point errors you get with single and double precision. Decimal is a much larger datatype than Currency and will support more than 4 decimal digits but that doesn't seem to be your problem and in the past Decimal had calculation problems so unless you need a fixed number of decimal digits higher than 4, then Currency is the best option.

If you actually want to limit the number of decimal places to just 2, you MUST round each calculation so that the rounding happens BEFORE the data is saved. Otherwise, you end up with as many decimal digits as were calculated if the data type is Single or Double or with precisely 4 decimal digits if you choose currency.

I even keep my percents as Currency to avoid floating point errors. I generally don't round these, but if I only want two decimal digits, I round in the calculation.

Scaled integer is an integer datatype with an assumed decimal position 4 from the right. So, 23.123 is stored internally as 231230. To place the decimal, Access divides the value by 1000.

 

[Gasman]

Not a biggy, but when I run a query that shows Factor * Carb/Sugar/Fibre I still get the £ sign, but not in my form?
I changed the numerics to Currency, format as Fixed and decimal places as 2.

 

[cheekybuddha]

Not a biggy, but when I run a query that shows Factor * Carb/Sugar/Fibre I still get the £ sign

 

[cheekybuddha]

I changed the numerics to Currency, format as Fixed and decimal places as 2

Doh! Finish reading the whole thing, Dave!

IIRC, you have to do it in the query designer too.

 

[cheekybuddha]

 

[Gasman]

Wow!, I have never ever set a format in a query.

Thank you David, learning something new all the time.

I had to Google to see how it was done.

 

[cheekybuddha]

Wow!, I have never ever set a format in a query.

IMHO, it's pretty irrelevant because one would never normally display a query in the query designer in any normal usage

 

[Gasman]

True, but handy to know anyway.
This is just for my personal use, so not making it bullet proof or even pretty, just functional.

 

[Pat Hartman]

If you set the default to standard in the table, it should propagate to the query but maybe not if you changed the default after the fact.

Do not forget my warning about using a format with two decimal places. Unless you actually want to round to 2 in the calculation, let the decimal places be 4 so that you are not hiding anything from yourself. When you open the table in DS view, it is imperative that you see what is actually there. If you don't, you'll be spending hours some day trying to figure out why the column total on the report doesn't match what your customer got with his calculator

 

[The_Doc_Man]

Speaking of using a calculator, it is important to know what KIND of calculator you are comparing against, since some of them are binary but some are BCD and thus round off differently for fractions. Can't remember exact model numbers, but back in my college days, some HP hand-held calculators were BCD internally. The one on your modern cell phone probably IS binary so would have similar rounding as your 'puter.

 

[Pat Hartman]

1.024 + 1.023 = 1.047 which rounds to 1.05
but the user sees
1.02 + 1.02 = 1.05 because internally, you are summing the actual value rather than the displayed value