Rounding to the nearest .25 of an hour

[hvns]

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

 

[dhookom]

I would create a small function that accepts a time and the number of minutes in an interval and returns the rounded time:

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]

 

[hvns]

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

 

[dhookom]

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]