Recordset .BOF returns false 1

[Rich8541]

Hello, I'm trying to test for BOF using a movelast then movefirst, but when I test for BOF, it returns false. Shouldn't a movefirst take me to the BOF of the recordset?
Thanks, Rich

 

[PHV]

I think BOF will be true if you try a MovePrev after the MoveFirst

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Sorry for the typo, I meant MovePrevious

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rich8541]

Thanks for the reply. I tried MovePrevious, but I get a No Current Record error when I check the value of the record. When I use MoveFirst, then check the value of the recordset, I'm actually on the first record. I'm trying to loop through the records(which are dates)and compare each record to a table of holidays and not count any of the holidays.
Rich

 

[PHV]

So, what is the problem ?
What is your code for looping and why do you feel concerned with the BOF property ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rich8541]

When I reach the end of the recordset using (MoveNext to step through the records) even though I use code to check for the end of the file (which returns false), I get a No Current Record" error.

                If rs_SixtyDayList.EOF = False Then
                      rs_SixtyDayList.MoveNext
                      MyDate = rs_SixtyDayList!WorkDate
                      
                ElseIf rs_SixtyDayList.EOF = True Then
                Exit Function
                End If

Rich

 

[PHV]

Again, what is the code of your loop ?
Furthermore, you should test the EOF property AFTER the MoveNext, not BEFORE.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rich8541]

Thanks PHV,
I got it to work, although not pretty it does the job. I borrowed some code called DeltaDays but can't remember where I got it from.

' Procedure : GetSixtyDayCount
' Author    : Jim
' Date      : 10/23/2013
' Purpose   : Determines the number of days working by getting the Start and Dates from table as opposed to calculating days between two dates. 
'             These dates are not consecutive. 
'
'---------------------------------------------------------------------------------------
'
Public Function GetSixtyDayCount() As Integer

    Dim SixtyDayStart As Date
    Dim SixtyDayEnd As Date
    Dim Dbas As DAO.Database
    Dim rs_SixtyDayList As DAO.Recordset
    Set Dbas = CurrentDb
    
    Set rs_SixtyDayList = Dbas.OpenRecordset("SELECT tblHours.WorkDate" & _
                                             " FROM tblHours" & _
                                             " GROUP BY tblHours.WorkDate" & _
                                             " HAVING (((tblHours.WorkDate) >= #6/26/2013#))" & _
                                             " ORDER BY tblHours.WorkDate;", dbOpenDynaset)
                                             
    With rs_SixtyDayList
        .MoveLast
        SixtyDayEnd = rs_SixtyDayList!WorkDate
        .MoveFirst
        SixtyDayStart = rs_SixtyDayList!WorkDate
    End With

    'Counts number of Days between two dates not counting holidays if they are in the table.
    'Does Not count Saturday or Sunday.
    Dim dbs As DAO.Database
    Dim rstHolidays As DAO.Recordset
    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Dim StartDate As Date
    Dim EndDate As Date

    NumSgn = VBA.Chr(35)
    MyDate = VBA.Format(SixtyDayStart, "Short date")

    Do While Not rs_SixtyDayList.EOF

        For Idx = CLng(SixtyDayStart) To CLng(SixtyDayEnd)
            Select Case (Weekday(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case 1 To 7      'Normal Workday
                strCriteria = "[Holidate] = " & NumSgn & VBA.Format$(MyDate, "yyyy-mm-dd") & NumSgn    'Thanks to "RoyVidar" 2/18/04
                rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                Else
                    'Do Nothing, it is NOT a Workday
                End If

            End Select

            If MyDate <> SixtyDayEnd Then
                rs_SixtyDayList.MoveNext
                MyDate = rs_SixtyDayList!WorkDate
            Else
                Exit Do

            End If

        Next Idx

    Loop

    GetSixtyDayCount = NumDays

    rs_SixtyDayList.Close
    Set rs_SixtyDayList = Nothing
    Set Dbas = Nothing

    rstHolidays.Close
    Set rstHolidays = Nothing
    Set dbs = Nothing

End Function