By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 435,380 Members | 3,074 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,380 IT Pros & Developers. It's quick & easy.

# Question on Code that calculates hours based on business hours

 P: 38 I have some code in my database that calculates time between two date/time fields and also calculates the weekends and holidays. I did not create this code, so I'm at a bit of a loss why it's not working correctly. I have to tables, one is called Working Hours, which has two fields, wStart and wEnd. In those fields, I have 08:30 and 18:30. The other table has the Holidays, two fields, hName, which has the name of the holiday, and hDate, which is the date of the holiday. Here is the code: Expand|Select|Wrap|Line Numbers Public Function HCalc(CtlS, CtlE, CtlReqCh) As Double ReqCh = CtlReqCh If ReqCh = False Or IsNull(CtlS) Or IsNull(CtlE) Then     HCalc = 0     Exit Function End If StDate = CtlS EnDate = CtlE StDateD = Format(StDate, "Short Date") EnDateD = Format(EnDate, "Short Date")     If StDateD = EnDateD Then Result = DateDiff("n", StDate, EnDate, vbUseSystemDayOfWeek) Else   Set qdefH = CurrentDb.CreateQueryDef("", "SELECT * FROM WorkingHours;") Set rstH = qdefH.OpenRecordset With rstH MinDay = DateDiff("n", !wStart, !wEnd, vbUseSystemDayOfWeek) Result = DateDiff("n", StDateT, !wEnd, vbUseSystemDayOfWeek) Result = Result + DateDiff("n", !wStart, EnDateT, vbUseSystemDayOfWeek) .Close   StDateT = Format(StDate, "Short Time") EnDateT = Format(EnDate, "Short Time") End With     Set qdefH = CurrentDb.CreateQueryDef("", "SELECT * FROM Holiday WHERE Weekday(hDate)<6 And Year(hDate) Between " & Year(StDateD) & " And " & Year(EnDateD) & ";") Set rstH = qdefH.OpenRecordset With rstH   StDateD = DateAdd("d", 1, StDateD)   Do Until StDateD = EnDateD     If Weekday(StDateD) < 6 Then         If .EOF = False Then             .MoveFirst             Do Until .EOF             If StDateD = !hDate Then                 Result = Result - MinDay                 Exit Do             End If           .MoveNext         Loop         End If         Result = Result + MinDay     End If     StDateD = DateAdd("d", 1, StDateD) Loop       .Close     End With     Set qdefH = Nothing     Set rstH = Nothing   End If   HCalc = Round(Result / 60, 2) End Function    The code is working, except when we have hours that go from Friday to Monday, it adds an extra 10 hours. During the week it works fine, and from Thursday to Monday it seems to work fine. I am still a bit of an amature when it comes to writing code, so I can not seem to find the glitch here. I would appreciate any help you can give me! I do not like when a problem is not consistent, and this one is baffling me. Thanks so much! Kelly Feb 10 '10 #1
Share this Question
10 Replies

 Expert 5K+ P: 8,638 I've only had a couple of minutes to look at the code, but try replacing Code Line #37 with the following. If this works, I'll then explain why: Expand|Select|Wrap|Line Numbers If Weekday(StDateD) <> 7 And Weekday(StDateD) <> 1 Then  Feb 10 '10 #2

 P: 38 The calculations seem to be working, but something strange is going on. When I use this code in a query, the results come up, but when I click on the field where the calculation resides, it keeps changing to what is in the record above. Here's what I put in the query: cTotalHours: HCalc([oDateSentTc],[oFinalDateT],Yes) oDateSentTC oFinalDateT cTotalHours 10/2/2009 3:22:04 PM 10/7/2009 1:37:00 PM 30.03 10/2/2009 3:22:13 PM 10/7/2009 1:37:00 PM 28.25 10/5/2009 11:45:00 AM 10/8/2009 11:47:00 AM 28.25 10/5/2009 9:17:12 AM 10/5/2009 3:53:37 PM 6.6 10/5/2009 11:45:00 AM 10/8/2009 11:47:00 AM 30.03 10/5/2009 9:17:12 AM 10/5/2009 3:53:37 PM 6.6 Two minutes later, I do it again: oDateSentTC oFinalDateT cTotalHours 10/2/2009 3:22:04 PM 10/7/2009 1:37:00 PM 28.25 10/2/2009 3:22:13 PM 10/7/2009 1:37:00 PM 28.25 10/5/2009 11:45:00 AM 10/8/2009 11:47:00 AM 28.25 10/5/2009 9:17:12 AM 10/5/2009 3:53:37 PM 6.6 10/5/2009 11:45:00 AM 10/8/2009 11:47:00 AM 30.03 10/5/2009 9:17:12 AM 10/5/2009 3:53:37 PM 6.6 10/5/2009 11:45:00 AM 10/8/2009 11:47:00 AM 30.03 See how the first calculation changed? If I click on the calculation, sometimes I get 30.03 and sometimes 28.85. It does this all over the place. Any ideas? Thanks again. Kelly Feb 10 '10 #3

 Expert 5K+ P: 8,638 Can you Attach the Database, or a Segment of it? Feb 10 '10 #4

 P: 38 How do I attach here? I could attach the test table that I've been using with the sql from the query. Feb 10 '10 #5

P: 38
Here is an example of the code and how it reacts. I have an example of the query. After you run the query, click on the results. The first one changes when you click on it.

Thanks again for all your help!
Attached Files
 TestCalcs.zip (37.0 KB, 60 views)
Feb 10 '10 #6

 Expert 5K+ P: 8,638 Sorry, do not have Access 2007, but can you covert the DB to Access 2000? I'm on vacation right now in Florida, and 2000 is the only Version that I can work with Feb 10 '10 #7

P: 38
Thanks so much for taking time out on your vacation!

Here it is in 2000 format.

Hate to sound desperate, but I am a little. :)

Kelly
Attached Files
 TestCalcs2000.zip (39.3 KB, 50 views)
Feb 10 '10 #8

 Expert 5K+ P: 8,638 Kelly, before I look into this, there is one major question. In Post #3 you indicate that you are passing a 3rd Argument to the HCalc() Function (Yes), namely: Expand|Select|Wrap|Line Numbers cTotalHours: HCalc([oDateSentTc],[oFinalDateT],Yes) This Function does not accept a 3rd Parameter, explanation? Feb 11 '10 #9

 P: 38 I took out the third argument before I sent you the update. We no longer need it. Kelly Feb 11 '10 #10

 Expert 5K+ P: 8,638 but when I click on the field where the calculation resides, it keeps changing to what is in the record above. Hello LSGKelly, I've tested the Query and could not duplicate what you are expriencing. Feb 12 '10 #11

### Post your reply

Sign in to post your reply or Sign up for a free account.