I am trying to take a set of data that is a series of consecutive dates in one field and another field that has a number of hours charged for that day. I want to take that set of data for the project and apply a bell curve idea to it where we say that 90% of the project was completed between specific dates.
Here is the code I am using. I have stolen snippets from various sources and now am getting a Run Time Error 91.
As you can see, I take the sum of hours and then find the midpoint. From there I take 90% of the sum and then figure out where the leftside and rightside are. What I am trying to do is loop until I find the date where the first 5% of the work has been done. Does that make sense? Is it possible?
In one example I have done by hand,
sumrecords = 1129
leftside = 56.45
rightside = 1072.55
the first three days of my data is:
ChargeDate SumOfHours
1/28/2008 27
1/29/2008 30.5
1/30/2008 23
So, I would like the code to go through and add SumOfHours until that sum is => than left side. In this case, it would be 1/29/08. I want 1/29/08 to be stored as firstdate.
Once I get this, I can do the same for the rightside.
Any help would be appreciated.
Here is the code I am using. I have stolen snippets from various sources and now am getting a Run Time Error 91.
Code:
Private Sub Command0_Click()
Dim sumrecords As Integer
sumrecords = DSum("[SumOfHours]", "Query1")
half = sumrecords / 2
ninety = sumrecords * 0.9
ninetyhalf = ninety / 2
leftside = half - ninetyhalf
rightside = half + ninetyhalf
Dim firstdatenum As Integer
Dim recordset As ADODB.recordset
Dim SQL As String
firstdatenum = 0
SQL = "SELECT * FROM Query1"
recordset.Open SQL, CurrentProject.Connection
Do Until firstdatenum > leftside
firstdatenum = firstdatenum + recordset("SumOfHours")
firstdate = recordset("ChargeDate")
recordset.MoveNext
Loop
MsgBox (firstdate)
recordset.Close
Set recordset = Nothing
End Sub
As you can see, I take the sum of hours and then find the midpoint. From there I take 90% of the sum and then figure out where the leftside and rightside are. What I am trying to do is loop until I find the date where the first 5% of the work has been done. Does that make sense? Is it possible?
In one example I have done by hand,
sumrecords = 1129
leftside = 56.45
rightside = 1072.55
the first three days of my data is:
ChargeDate SumOfHours
1/28/2008 27
1/29/2008 30.5
1/30/2008 23
So, I would like the code to go through and add SumOfHours until that sum is => than left side. In this case, it would be 1/29/08. I want 1/29/08 to be stored as firstdate.
Once I get this, I can do the same for the rightside.
Any help would be appreciated.