Solved Average Speed, Decimal Places and Frustration

[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...