|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 19th, 2008, 09:04 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Rounding Time?
Hello,
I am needing some assistance with the following: I want to take someones hours for the day and round them with the following rule = If you work 5.01 to 5.5 hours for that day to round to 5.5 If you work 5.51 to 5.99 hours for that day to round to 6.0 (next whole number) Is this something that I can do in a query? Any help is appreciated, John 
|
|
#2
November 19th, 2008, 12:51 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Rounding Time?
You can try the following expression.
-Int(-[WorkTime]*2)/2 It should work for you. It rounds up to the next half hour for all positive numbers. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County John wrote: Hello, I am needing some assistance with the following: I want to take someones hours for the day and round them with the following rule = If you work 5.01 to 5.5 hours for that day to round to 5.5 If you work 5.51 to 5.99 hours for that day to round to 6.0 (next whole number) Is this something that I can do in a query? Any help is appreciated, John
|
|
#3
November 19th, 2008, 02:57 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Rounding Time?
John - I tried the formula and it is not rounding to what I need.
examples - if I work 0.02 hours I need it to display 0.50 hours (round up to the next half hour) - if I work 0.51 hours I need it to display 1.0 hours (round up to the next whole number) the formula below returns the following: 0.02 = 0.00 "John Spencer" wrote: You can try the following expression. -Int(-[WorkTime]*2)/2 It should work for you. It rounds up to the next half hour for all positive numbers. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County John wrote: Hello, I am needing some assistance with the following: I want to take someones hours for the day and round them with the following rule = If you work 5.01 to 5.5 hours for that day to round to 5.5 If you work 5.51 to 5.99 hours for that day to round to 6.0 (next whole number) Is this something that I can do in a query? Any help is appreciated, John
|
|
#4
November 19th, 2008, 05:42 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Rounding Time?
Strange. I just tested it with this in my VBA immediate window
WorkTime=.02 ?-Int(-WorkTime*2)/2 and the result is 0.5 What kind of field are you putting the value into? Both integer and Long Integer only handle integer values (no fractional or decimal part). If you try to put .5 into a number field that is type integer you will get zero returned. The value will be rounded using banker's rounding. That is numbers that end in 5 will be rounded to the nearest EVEN number. *** 1.5 and 2.5 both round to 2 *** .5 will round to Zero John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County John wrote: John - I tried the formula and it is not rounding to what I need. examples - if I work 0.02 hours I need it to display 0.50 hours (round up to the next half hour) - if I work 0.51 hours I need it to display 1.0 hours (round up to the next whole number) the formula below returns the following: 0.02 = 0.00 "John Spencer" wrote: You can try the following expression. -Int(-[WorkTime]*2)/2 It should work for you. It rounds up to the next half hour for all positive numbers. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County John wrote: Hello, I am needing some assistance with the following: I want to take someones hours for the day and round them with the following rule = If you work 5.01 to 5.5 hours for that day to round to 5.5 If you work 5.51 to 5.99 hours for that day to round to 6.0 (next whole number) Is this something that I can do in a query? Any help is appreciated, John
|
|
#5
November 19th, 2008, 10:11 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Rounding Time?
John S... here is the formual that finally worked for me:
INT( Y + 0.49 ) + ( 0.50 * INT( ( ( Y + 0.50 ) - INT( Y + 0.49 ) ) / 0.51 ) ) "John Spencer" wrote: Strange. I just tested it with this in my VBA immediate window WorkTime=.02 ?-Int(-WorkTime*2)/2 and the result is 0.5 What kind of field are you putting the value into? Both integer and Long Integer only handle integer values (no fractional or decimal part). If you try to put .5 into a number field that is type integer you will get zero returned. The value will be rounded using banker's rounding. That is numbers that end in 5 will be rounded to the nearest EVEN number. *** 1.5 and 2.5 both round to 2 *** .5 will round to Zero John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County John wrote: John - I tried the formula and it is not rounding to what I need. examples - if I work 0.02 hours I need it to display 0.50 hours (round up to the next half hour) - if I work 0.51 hours I need it to display 1.0 hours (round up to the next whole number) the formula below returns the following: 0.02 = 0.00 "John Spencer" wrote: You can try the following expression. -Int(-[WorkTime]*2)/2 It should work for you. It rounds up to the next half hour for all positive numbers. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County John wrote: Hello, I am needing some assistance with the following: I want to take someones hours for the day and round them with the following rule = If you work 5.01 to 5.5 hours for that day to round to 5.5 If you work 5.51 to 5.99 hours for that day to round to 6.0 (next whole number) Is this something that I can do in a query? Any help is appreciated, John
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
