Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Rounding to the nearest .25 of an hour

Status
Not open for further replies.

hvns

Technical User
Oct 2, 2007
25
US
Hello all,

I am trying to round for the first time in a query...
and would like to have the query round to the nearest quarter hour...
Example date:
Hours
1.16
2.73
6.40

Desired results:
1.25
2.75
6.50

to have the hours worked rounded to the nearest quarter hour. Up or Down...

Thanks in advance for the help...
John
 
I would create a small function that accepts a time and the number of minutes in an interval and returns the rounded time:
Code:
Public Function RoundTime(datTime As Date, intMinutes As Integer) As Date
    RoundTime = CInt(datTime * 1440 / intMinutes) * intMinutes / 1440
End Function
To get from hours to a time value, divide by 24. To get from time back to hours, multiply by 24

? RoundTime(1.16/24, 15) * 24


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
problem is that I am reading time from a linked table that is read only.. other issue never done functions so I was hoping to do it right in the query...

I have seen the following expressions used on the net but for some reason it does not work for me:

RoundedTime: Sum(Round(([LDHoursWorked])/24,15)*24)
RoundedTime2: Sum(Round(([LDHoursWorked])*96,0)/96)

LDHoursWorked Sum = 15.97
after above expressions = 15.97
 
Read only status doesn't make any difference.
Code 101 ;-)
Open a new blank module. Copy the three lines of code from above into the new module. Save the module with the name "basDateTimeCalcs".

You can then create a query or control source using the function much like you would use any other function.

What is the data type of your Hours value?

A simpler expression is
Cint([LDHoursWorked] * 4)/4

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top