Question on Code that calculates hours based on business hours

[LSGKelly]

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:

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

[ADezii]

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:

If Weekday(StDateD) <> 7 And Weekday(StDateD) <> 1 Then 

[LSGKelly]

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

[ADezii]

Can you Attach the Database, or a Segment of it?

[LSGKelly]

How do I attach here? I could attach the test table that I've been using with the sql from the query.

[LSGKelly]

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!

[ADezii]

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

[LSGKelly]

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

[ADezii]

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:

cTotalHours: HCalc([oDateSentTc],[oFinalDateT],Yes)

This Function does not accept a 3rd Parameter, explanation?

[LSGKelly]

I took out the third argument before I sent you the update. We no longer need it.

Kelly

[ADezii]

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.