Converting minutes to hours and tenths of hour

[cookie5]

I am working on a query in a db that requires me to determine elapsed time. I have the total minutes but when I divide by 60 what I get is the format hh.mmmmmmmm. Even if I set the property of the field to fixed with 1 decimal, when I use the field in another calculation to determine an hourly cost, it converts it back to the hh.mmmmmmmmetc format. What I need is for the minutes to be converted to tenths of an hour. For example 70 minutes is 1.1666666667 I need it to round to 1.2.
Any help?
Thanks in advance

 

[PaulBricker]

Use this
Round([Minutes]/60,1)

That will give you tenths of an hour rounded to one decimal place.

Paul

 

[cookie5]

Thanks Paul but when I enter the code I get the error message
Unknown function 'Round' in expression

any ideas?

 

[PaulBricker]

What version of Access are you using. As far as I know, 97 had the Round function, and I'm using 2000 and it works in that. You might check your References. Open any module new or existing. On the menu bar go to Tools...References and make sure the Visual Basic for Applications and the Microsoft Access 9.0 Object Library have check marks next to them. I don't really know where the Round function is but I would guess one of those two libraries.

Paul

 

[cookie5]

I am using Access 97. I checked my references. Both Visual Basic for applications and Microsoft Access 8.0 Object Library were checked. Any other ideas? I appreciate your help.

 

[PaulBricker]

Have you checked Help to see if the Round function is shown. Take a look and in the mean time I will try and write something to do the same thing.

Paul

 

[cookie5]

Yes, I have looked in help for Round, it was not there. Thanks for any help you can give me.

 

[PaulBricker]

Try this. Just put it in a column in your query and change the Minutes value to the name of your minutes field.

MyTime:Int(((([Minutes]/60)+.05)*100)/10)*.1


Paul

 

[cookie5]

Paul,
That works great, thanks. Tell me, what is the +.05 for in the expression?
Thanks again.

 

[PaulBricker]

It is a factoring agent. When you divide 70/60 you get 1.16. We want the value .16 to become .2 so we add the factor to it. .16 + .05 = .21. Then we drop the thousandths value and that leaves us .2 or 1.2 for the total value. If our Minutes/60 = x.n0 to x.n4 then when we add the factoring agent, our value for n doesn't change so when we drop the thousandths we get x.n (in other words we've rounded n down). If our Minutes/60 = x.n5 to x.n9 then when we add .05 to that we get x.(n+1)(we've rounded n up). This is clear as mud? Simply put, it's a way to maniipulate the value so that it look like any thousands value between 0 and 4 rounds down and anything between 5 and 9 rounds up.

Paul

 

[cookie5]

Got it. Thanks again!!!!