Recordset looping fails to compute in ms access 2007

[MfanyaKitu]

Hi there.
I'm trying to write vba code to loop through a table and add to the total wherever a certain condition is met, based on the form input. Here is the code:

'Assign date values(form input) into variables for easy manipulation
    Dim StartDate As Date, EndDate As Date
        StartDate = Me.datStart.Value
        EndDate = Me.datEnd.Value
    
    'Create recordsets for storing database objects
    Dim myRevenueSet As DAO.Recordset
    
    'Declare variable to store amounts
    Dim curRevenueSum As Currency
    curRevenueSum = 0
    
    'Create SQL statement to be used during looping
    Dim myRevenueSQL As String
    myRevenueSQL = "SELECT datRevenue, curRevenueAmount FROM tblRevenue;"
       
    Set myRevenueSet = DBEngine(0)(0).OpenRecordset(myRevenueSQL, dbOpenDynaset)
      
      'Start to loop in a table
      Do While Not myRevenueSet.EOF
          
      'Check to see if the current record is within date range and if so add its value to a total
             If DateDiff("d", StartDate, myRevenueSet!datRevenue) <= 0 And DateDiff("d", EndDate, myRevenueSet!datRevenue) >= 0 Then
              curRevenueSum = curRevenueSum + myRevenueSet!curRevenueAmount
             End If
            'Move to the next record within the table
             myRevenueSet.MoveNext
      Loop
      'Assign totals in specified textbox in the form
      Me.curSales.Value = curRevenueSum
      'Reset the recordset object
      myRevenueSet.Close
      Set myRevenueSet = Nothing

I don't see anything wrong with the code, but it doesn't work. Anybody with the idea of what am I missing?

[twinnyfo]

When and where is this code executing? How does it not work? Are you getting an error? Is it returning wrong values? Is it doing nothing?

Please explain so we can guide you to a solution.

[MfanyaKitu]

I have put the code on the onClick event of the button that is on the input form, the same form where the result is supposed to be displayed, but on different textbox.
The code does nothing, retuns neither an error nor wrong values. Hence the textbox results dispay only its default value 0.0 as I have set it to the currency data type.
Hope this explanation will help.

[twinnyfo]

I really think you are over-complicating things. This could be much slower if you had thousands and thousands of records.

Here is all you need:

    Dim myRevenueSet As DAO.Recordset
    Dim myRevenueSQL As String
    Dim curRevenueSum As Currency
    curRevenueSum = 0
    myRevenueSQL = "SELECT Sum(curRevenueAmount) AS TotalRevenue " & _
        "FROM tblRevenue " & _
        "WHERE datRevenue >= #" & Me.datStart & _
        "# AND datRevenue <= #" & Me.datEnd & "#;"
    Set myRevenueSet = DBEngine(0)(0).OpenRecordset(myRevenueSQL, dbOpenDynaset)
    If Not myRevenueSet.EOF Then
        curRevenueSum = myRevenueSet!TotalRevenue
    Else
        currevenusum = 0
    End If
    Me.curSales.Value = curRevenueSum
    myRevenueSet.Close
    Set myRevenueSet = Nothing

I would also recommend that you validate that the Start Date is before the End date, otherwise you could get some unexpected results.

Hope this helps!

[MfanyaKitu]

Thanks for the reply twinnyfo. I at last manage to spot the real problem and make it work by the help of your code version and mostly by your ending hint;

I would also recommend that you validate that the Start Date is before the End date, otherwise you could get some unexpected results.

The problem was with the logic that I used in conditional statement while validating the Start Date and the End Date.
Thanks again for your help.

[twinnyfo]

Glad I could be of service. Hope to see you on this forum again in the future!

[MfanyaKitu]

Pretty sure about that. This is the second time I get help within hours for what has been my headache for days. So much appreciating the readiness of you guys to help.

[MfanyaKitu]

For the sake of forum users:
If you are to use the code provided in post #4, it will work only if your data returns something. But when it returns nothing, such as it returns Null, an error 'invalid use of null' will occur. In order to avoid that, change that code into :

Dim myRevenueSet As DAO.Recordset
Dim myRevenueSQL As String
Dim curRevenueSum As Currency
  curRevenueSum = 0
  myRevenueSQL = "SELECT curRevenueAmount " & _
        "FROM tblRevenue " & _
        "WHERE datRevenue >= #" & Me.datStart & _
        "# AND datRevenue <= #" & Me.datEnd & "#;"
Set myRevenueSet = DBEngine(0)(0).OpenRecordset(myRevenueSQL, dbOpenDynaset)
Do While Not myRevenueSet.EOF
 curRevenueSum = curRevenueSum + myRevenueSet!curRevenueAmount
 myRevenueSet.MoveNext
Loop
Me.curSales.Value = curRevenueSum
myRevenueSet.Close
Set myRevenueSet = Nothing

All the best!