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
Bytes IT Community
+ 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
  1. Public Function HCalc(CtlS, CtlE, CtlReqCh) As Double
  2. ReqCh = CtlReqCh
  3. If ReqCh = False Or IsNull(CtlS) Or IsNull(CtlE) Then
  4.     HCalc = 0
  5.     Exit Function
  6. End If
  7. StDate = CtlS
  8. EnDate = CtlE
  9. StDateD = Format(StDate, "Short Date")
  10. EnDateD = Format(EnDate, "Short Date")
  11.  
  12.  
  13. If StDateD = EnDateD Then
  14. Result = DateDiff("n", StDate, EnDate, vbUseSystemDayOfWeek)
  15. Else
  16.  
  17. Set qdefH = CurrentDb.CreateQueryDef("", "SELECT * FROM WorkingHours;")
  18. Set rstH = qdefH.OpenRecordset
  19. With rstH
  20. MinDay = DateDiff("n", !wStart, !wEnd, vbUseSystemDayOfWeek)
  21. Result = DateDiff("n", StDateT, !wEnd, vbUseSystemDayOfWeek)
  22. Result = Result + DateDiff("n", !wStart, EnDateT, vbUseSystemDayOfWeek)
  23. .Close
  24.  
  25. StDateT = Format(StDate, "Short Time")
  26. EnDateT = Format(EnDate, "Short Time")
  27. End With
  28.  
  29.  
  30. Set qdefH = CurrentDb.CreateQueryDef("", "SELECT * FROM Holiday WHERE Weekday(hDate)<6 And Year(hDate) Between " & Year(StDateD) & " And " & Year(EnDateD) & ";")
  31. Set rstH = qdefH.OpenRecordset
  32. With rstH
  33.  
  34. StDateD = DateAdd("d", 1, StDateD)
  35.  
  36. Do Until StDateD = EnDateD
  37.     If Weekday(StDateD) < 6 Then
  38.         If .EOF = False Then
  39.             .MoveFirst
  40.             Do Until .EOF
  41.             If StDateD = !hDate Then
  42.                 Result = Result - MinDay
  43.                 Exit Do
  44.             End If
  45.  
  46.         .MoveNext
  47.         Loop
  48.         End If
  49.         Result = Result + MinDay
  50.     End If
  51.     StDateD = DateAdd("d", 1, StDateD)
  52. Loop
  53.  
  54.     .Close
  55.     End With
  56.     Set qdefH = Nothing
  57.     Set rstH = Nothing
  58.  
  59. End If
  60.  
  61. HCalc = Round(Result / 60, 2)
  62. End Function 
  63.  
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
Share on Google+
10 Replies


ADezii
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
  1. 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

ADezii
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
File Type: zip TestCalcs.zip (37.0 KB, 60 views)
Feb 10 '10 #6

ADezii
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
File Type: zip TestCalcs2000.zip (39.3 KB, 50 views)
Feb 10 '10 #8

ADezii
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
  1. 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

ADezii
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.