Solved Average Speed, Decimal Places and Frustration (1 Viewer)

[Gimlett]

Hello,

I fear I have been dabbling again and am stuck despite good progress made in my application for my running club thanks to the help from this forum.

I am getting frustrated now as I frequently meet this decimal place problem.

I have been able to overcome this previously by simply shortening the text box control on a form or report to visually limit the numbers after the decimal place despite the quantity of numbers produced in the query.

This is my calculated field in a query as the control source for my report: AverageSpeed: (3.8/[TotalSeconds])*3600

Total seconds is a calculated field in the query which produces satisfactory results.

I'm trying to work out average speed in mph for a report based on this query using calculated field in query as above.

It works in the query but produces so many numbers after the decimal point, I can't open the field in the report enough to accommodate these numbers. I just get hashes I only want two decimal places.

I try to alter decimal places in the report properties but it has no effect. I don't understand what is going on here. Why does access provide this option but has no affect?

Please what am I not understanding?

Speed = distance divided by time

Average speed = dst 3.8 / totalseconds * 3600

11.2778235779060181368507832 mph

Many Thanks

Chris

 

[ebs17]

In mathematics, rounding is used to reduce decimal places.

 

[MajP]

Make sure you set the decimal places, AND select Fixed

Textbox on left is 2 decimal places and fixed, the other is 2 decimal places and General. In second case does nothing as you said

 

[Gimlett]

I'm so sorry, I'm beginning to think I'm not cut out for this.

Thank you MajP

 

[Gasman]

Perservere.
Some people have been playing with Access since 2005, and they still cannot get the hang of it.

 

[MajP]

I'm so sorry, I'm beginning to think I'm not cut out for this.

Thank you MajP

Trust me, you are not the first doing that. The reason I know the answer is after lots of banging my own head.

However there are lots of other ways to approach this
Example, In your query you could apply a format or round the value as mentioned.
AverageSpeed: round((3.8/[TotalSeconds])*3600,2)
AverageSpeed: Format((3.8/[TotalSeconds])*3600,"00.00")

 

[MajP]

The one that still gets me all the time is Sorting and Grouping applied to a report which overrides the sort order at the query. You change the sort order and nothing happens. You are about to rebuild the report when you remember to open up sorting and grouping. Another hidden one is the forms Order By and Filter properties. These two can get inadvertently set at design time and you cannot figure out what is going on.

 

[isladogs]

As suggested by MajP, I would set the number of d.p. and the format as fixed

This means the underlying data is unaltered.
That also means that if you click on the control containing the data, it will show all the decimal places in the actual data

 

[Gimlett]

Thanks again,

All the points you mention are very relevant to my situation, I guess Access is so massive (probably true of a lot of software) that there's so much to explore in the depth of it. The examples you provide MajP are incredibly useful, I can put those into practice from now on. I know what you mean with the sorting and grouping of reports, that took some figuring out. I don't think the reports always had that feature in earlier versions?

Thanks too isaldogs that's useful info and more I can experiment with.

 

[Jason Lee Hayes]

Perservere.
Some people have been playing with Access since 2005, and they still cannot get the hang of it.

2 steps forward 1 step back. It's the only way we learn.. Never give up; never give in...

 

[Pat Hartman]

I don't think the reports always had that feature in earlier versions?

It's been around as long as I can remember since it confused me back in the 90's. Another tricky thing that you would only run into if you write code that references fields that are not bound to controls is that Access takes it upon itself to rewrite your RecordSource table/query to eliminate any columns that are not bound to a control. So, for example, if you don't show the tax % in a control but use it co calculate the tax amount, in VBA, you will run into a problem after you save the report and move on because next time you run it, the tax % won't be in the RecordSource so your code will raise an error. The only way to get around it is to do the calculation in the query or bind the % to a hidden control on the report.

Once you start doing calculations that will result in decimal places beyond what you want to show, you need to decide what to do with them. If I truly don't care, I round to two places (or whatever I want to show) when I perform the calculation. That makes the precision a non-issue for everything else. If I care, there are other problems. For example, one of my early jobs in the industry was for a portfolio manager and we kept our data at four decimal places but on reports to the customer, we only wanted to show two places. Well, if there are enough items in the portfolio, the total won't reflect the sum of the items if the customer got out his calculator and decided to add up the hundred positions in his portfolio unless you sum the rounded versions rather than the raw versions to calculate the total