Rounding on Calculated results (1 Viewer)

[Goody974]

Hi All

I am trying to fix an issue with my database and hoping someone has the knowledge to help with this.

In its most simplified format I have 3 fields. One is the load applied, the second is the result noted and the 3rd field is calculated as the result minus the load applied.

You should end up seeing 500.0, 499.0, -1.0 in their respective boxes on the report.

What I actually end up with is 500.0, 499.0, -1

I have tried various settings for each of these fields but it never seems to work right. Currently I have the first 2 fields as short text and the third field as calculated.

Its not always a 1 decimal place result I need. Sometimes it may be whole numbers, sometimes it may be 2 decimal places, so I need this to be automatic.

Does anyone have any pointers please?

 

[theDBguy]

Hi. In the Report's design view, select the calculated control and set the Format property to show decimal places (try Fixed, for example). Hope it helps...

 

[Goody974]

Thanks theDBGuy, doing this has made the result come out to 2 decimal places when there should only be 1 in the given example... and also sometimes the load and the result will need to be to different decimal places.

Somehow I think this is to do with the calculation as the calculated result is shown as -1.00, even though the 2 inputs are 1 decimal place.

 

[theDBguy]

Thanks theDBGuy, doing this has made the result come out to 2 decimal places when there should only be 1 in the given example... and also sometimes the load and the result will need to be to different decimal places.

Somehow I think this is to do with the calculation as the calculated result is shown as -1.00, even though the 2 inputs are 1 decimal place.

Did you select Fixed? If so, did you also specify the number of decimal places to 1?

 

[Goody974]

Did you select Fixed? If so, did you also specify the number of decimal places to 1?

Okay that works, but what do I then do for the results I need to show in whole numbers or 2 decimal places? I dont really want the staff changing these boxes for each different report they need to print.

 

[theDBguy]

Hi. Typically, numbers don't have leading or trailing zeroes. So, if you leave the formatting alone, then you won't see them. But, if you want to see them, you'll have to instruct the computer to display them and also the number of zeroes you want shown. If you don't say how many you want, the computer won't know how many zeroes to include (remember, leading and trailing zeroes can go forever, right?).

 

[Goody974]

Hi. Typically, numbers don't have leading or trailing zeroes. So, if you leave the formatting alone, then you won't see them. But, if you want to see them, you'll have to instruct the computer to display them and also the number of zeroes you want shown. If you don't say how many you want, the computer won't know how many zeroes to include (remember, leading and trailing zeroes can go forever, right?).

Thanks, but I thought if I have 2 fields that have numbers with 1 decimal place and the calculated results decimal place is set to auto, why doesnt it show the result in 1 decimal place?

 

[theDBguy]

Thanks, but I thought if I have 2 fields that have numbers with 1 decimal place and the calculated results decimal place is set to auto, why doesnt it show the result in 1 decimal place?

Hi. Auto means it will display as many decimal places is needed (up to a point, I believe). For example, if you divide two numbers like 50/20, you'll get 2.5. But if you do it like 50/40, you'll get 1.25. However, if you do it like 50/15, then you could get something like 3.3333333333333333333333333333333333

 

[Goody974]

ok think I understand. So is it possible to have access read a field and decipher how many decimal places are in there and then format the results field to that automatically?

 

[theDBguy]

ok think I understand. So is it possible to have access read a field and decipher how many decimal places are in there and then format the results field to that automatically?

That's what Auto does, except leading and trailing zeroes are truncated. Are you asking to "read" how many decimal places in each of the other fields and then automatically set the number of decimal places to match in the result? This might be possible but I think it might affect other results as well. Unless, you convert the result from a number to a text. Now, text values can have leading and trailing zeroes.

 

[Goody974]

That's what Auto does, except leading and trailing zeroes are truncated. Are you asking to "read" how many decimal places in each of the other fields and then automatically set the number of decimal places to match in the result? This might be possible but I think it might affect other results as well. Unless, you convert the result from a number to a text. Now, text values can have leading and trailing zeroes.

Wow... I find it strange how its so awkward to achieve.

All I want is for a result to be in the same decimal format as the input.

1.5 + 1.0 = 2.5
1 + 2 = 3
1.08 - 1.00 = 0.08

I dont want the staff to have access to change the design values of the database, so I either find a way of access doing the above automatically, or have a field that the staff member can choose how many decimal places for each report by way of a drop down box on the form.

 

[theDBguy]

Wow... I find it strange how its so awkward to achieve.

All I want is for a result to be in the same decimal format as the input.

1.5 + 1.0 = 2.5
1 + 2 = 3
1.08 - 1.00 = 0.08

I dont want the staff to have access to change the design values of the database, so I either find a way of access doing the above automatically, or have a field that the staff member can choose how many decimal places for each report by way of a drop down box on the form.

Hi. None of the sample data you provided should be a problem, except for the last one, which is because of another issue - rounding (notice the 1 at the very end?). But it should still display fine, I would expect.

But, as I was saying earlier, if you want something other than the default behavior, then you'll have to create your own routine to do it. Here's an example to find out the number of decimal places for one input value.

Len(Mid(1.5,InStr(1.5,".")+1))

Unfortunately though, this won't work with trailing zeroes, because as I already mentioned, computers don't "see" trailing zeroes in numbers. It's just for humans.

 

[Goody974]

Hi. None of the sample data you provided should be a problem, except for the last one, which is because of another issue - rounding (notice the 1 at the very end?). But it should still display fine, I would expect.

But, as I was saying earlier, if you want something other than the default behavior, then you'll have to create your own routine to do it. Here's an example to find out the number of decimal places for one input value.

Len(Mid(1.5,InStr(1.5,".")+1))

Unfortunately though, this won't work with trailing zeroes, because as I already mentioned, computers don't "see" trailing zeroes in numbers. It's just for humans.

thank you for your help. I will need to play around with this and see if I can get it to work how I want it to. Appreciate your time.

 

[theDBguy]

thank you for your help. I will need to play around with this and see if I can get it to work how I want it to. Appreciate your time.

Hi. Good luck! Sorry I wasn't able to help much. Cheers!

 

[Mark_]

Something to note, 2.5 - 1.5 = 1, so you would normally not expect it to show as 1.0. Likewise if someone keyed in 2.5000 -1, what would you expect the results to be?

 

[Goody974]

Something to note, 2.5 - 1.5 = 1, so you would normally not expect it to show as 1.0. Likewise if someone keyed in 2.5000 -1, what would you expect the results to be?

Yeah I understand what you are saying I just need to work out a way for it to show the results better for my application.

The first column is a load applied to a scale eg, 1000Kg

The second column is what the scale read in its own increments, eg 1000.5

The third column is the error, in this case 0.5

This can be one decimal place on one scale, and 4 decimal places on another scale (eg 2.0000Kg, 1.9998Kg, -0.0002Kg)

Thats why I cant have a fixed decimal place format, but not sure how I can overcome this?

 

[theDBguy]

This can be one decimal place on one scale, and 4 decimal places on another scale (eg 2.0000Kg, 1.9998Kg, -0.0002Kg)

Thats why I cant have a fixed decimal place format, but not sure how I can overcome this?

Hi. Again, the example you've given should not be a problem.

It's when the result has trailing zeroes where you're potentially having an issue. Can you please explain to us what is the significance between displaying 2Kg and 2.0Kg? Thanks!

 

[Goody974]

Thanks DBGuy

Its because we are supposed to show what the scale read exactly, Its for calibration certificates. We cant round down, it needs to actually report what the scale read in the denomination it was set in (g, kg, t etc)

In your example there, have you got the format to fixed with 4 decimal places? wondering why it hasnt truncated the 2.0000 in Text0

 

[theDBguy]

Thanks DBGuy

Its because we are supposed to show what the scale read exactly, Its for calibration certificates. We cant round down, it needs to actually report what the scale read in the denomination it was set in (g, kg, t etc)

In your example there, have you got the format to fixed with 4 decimal places? wondering why it hasnt truncated the 2.0000 in Text0

Hi. No, I didn't set any formatting in the textboxes. I was using unbound textboxes in this case, so I just typed in 2.0000, which Access probably assumed as Text instead of a number. Which is probably another approach you could try to use. That is, store your scale readings as Text and convert them into numbers for the calculations, and then display the result as Text again. Just a thought...

 

[The_Doc_Man]

You asked the question:

So is it possible to have access read a field and decipher how many decimal places are in there and then format the results field to that automatically?

You have gotten various answers that suggest it isn't as easy as you would like. I'll be more direct. The answer is NO unless you want to write a LOT of code and confuse yourself badly.

The short explanation is that normally, everything you store goes to its own variable that has its own setup parameters included format, decimal places, fixed/float on the decimal point, etc. Access DOES NOT CARE about the decimal places that were in the contributory numbers. It cares about the data type in which the answers are stored.

There is a further fly in the ointment. If you are dealing with fractional numbers, you are using either SINGLE, DOUBLE, scaled LONG, or CURRENCY data types. Scaled integers and currency are special cases, but SINGLE and DOUBLE both have the same problem.

In decimal, if you write the fraction 1/3 you get 0.333333333... as an infinitely repeating decimal fraction. In binary, if you write the decimal fraction 1/10 (by which I mean 0.1 in decimal), you get 0.00011001100110011..., an infinitely repeating binary fraction. So that difference of "1.08 - 1.00 = 0.08" actually involves rounding and truncation behind the scenes because the computer has to approximate decimal numbers with binary fractions that don't come out even.

Is it possible to write code that self-adjusts the number of decimal places? Yes, absolutely so. But it is not a trivial problem.