Code Help Needed - VBA

asatyss

New member
Local time
Today, 06:44
Joined
Oct 16, 2008
Messages
5
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.
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.
 
Welcome to the forum:
Best way to get help with a specific error is:
- Supply the error description instead of the number. Invalid use of Null? Overflow? Division by Zero? Application or Object defined error? I don't memorize the numbers.
- Indicate on which line the error occurs.
Cheers,
 
Thanks, the error occurs on the following line:

recordset.Open SQL, CurrentProject.Connection

The error is "Object variable or With Block variable not set"
 
you have to declare a recordset object and assign it instead of trying to use recordset

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
...your other code here

rst.Open SQL, CurrentProject.Connection

etc.
 
That worked, I knew it was something liek that, I just couldn't figure it out.

Thanks for your help!
 

Users who are viewing this thread

Back
Top Bottom