Rounding Weeks in a Month (1 Viewer)

[ebarrera]

Need some help with yet another rounding issue. The data in bold are the only two pieces of data I have given to me. This in red are calculated through a series of queries.

StartDate = 06/09/08
EndDate = 08/01/08
Weeks = 8
TotalDays = 54
TotalWeeks = (54/7) = 7.71
Month1Days = 22
Month2Days = 31
Month3Days = 1
Month4Days = 0
Month5Days = 0
Month1Weeks = 22/7 = 3.14
Month2Weeks = 31/7 = 4.43
Month3Weeks = 1/7 = 0.14
Month4Weeks = 0/7 = 0.00
Month5Weeks = 0/7 = 0.00

Need to get one of the following results.

CORRECTION
Month1WksRounded = 4.00
Month2WksRounded = 4.00
Month3WksRounded = 0.00
Month4WksRounded = 0.00
Month5WksRounded = 0.00
TotalWksRounded = 8.00

I tried the nearest half function (Int(sValue * 2 + 0.5) / 2) that I found in this forum and ran into a problem when I had a value less than 0.5. I am guessing that the function would round up, for any value greater than 0.5. If value 0.00 - 0.49 should round down.

As you can see by the time on this posting, I am stuck and will try again in the morning. Any assistance would be greatly appreciated.

 

[Jon K]

Try this:

Int(sValue +0.5)
.

 

[ebarrera]

Thanks John, but I just ran into a problem with the way that our payroll pays and would need to determine how many meetings in a month, so I set up some queries that do this for me. I am now stuck with yet another rounding issue. I thought I had it, but I am getting a "query is too complex" error. Any help is greatly appreciated.

Our payroll department pays out the remaining hours in the last month. I came up with the following iif() statements to check for last paycheck and if true, pay the monthly hours plus the difference.

Mon1HrsTBA: IIf([EndMonthPay]>1,Round([Mon1MeetTBA]*[DailyTBA],2),IIf([EndMonthPay]=1,[SemTBA],0))

Mon2HrsTBA: IIf([StartMonthPay]=2 And [EndMonthPay]=2,[SemTBA],IIf([EndMonthPay]>2,Round([Mon2MeetTBA]*[DailyTBA],2),IIf([EndMonthPay]=2 And [StartMonthPay]<2,[SemTBA]-[Mon1HrsTBA],0)))

Mon3HrsTBA: IIf([StartMonthPay]=3 And [EndMonthPay]=3,[SemTBA],IIf([EndMonthPay]>3,Round([Mon3MeetTBA]*[DailyTBA],2),IIf([EndMonthPay]=3 And [StartMonthPay]<3,[SemTBA]-([Mon1HrsTBA]+[Mon2HrsTBA]),0)))

Mon4HrsTBA: IIf([StartMonthPay]=4 And [EndMonthPay]=4,[SemTBA],IIf([EndMonthPay]>4,Round([Mon4MeetTBA]*[DailyTBA],2),IIf([EndMonthPay]=4 And [StartMonthPay]<4,[SemTBA]-([Mon1HrsTBA]+[Mon2HrsTBA]+[Mon3HrsTBA]),0)))

Mon5HrsTBA: IIf([StartMonthPay]=5 And [EndMonthPay]=5,[SemTBA],IIf([EndMonthPay]=5 And [StartMonthPay]<5,[SemTBA]-([Mon1HrsTBA]+[Mon2HrsTBA]+[Mon3HrsTBA]+[Mon4HrsTBA]),0))

 

[ebarrera]

I just ran each field and it looks like the prolem lies when I enter Mon4HrsTBA. I modified the query (removing the addition of Mon3HrsTBA) and it works. I may have to simplify the query.