Hi All,
The following functions return the next Sunday date from a given date. I am trying to find all the Sunday dates between two dates. Running the following function once returns the correct information. If I add the Do Loop the function just continues to run and returns incorrect data. (Examples of data returned below.)
Would someone explain to me what I am missing here?
Thanks.
Public Function SundayDates()
Dim db As Database
Dim rst As Recordset
Dim Inputdate As Date
Dim EndDate As Date
Dim HoldDate As Date
Set db = CurrentDb
Set rst = db.OpenRecordset(“DateRange”)
If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
Else
‘Do something else
End If
Inputdate = rst![BDate]
EndDate = rst![EDate]
‘Do
HoldDate = NextSunday(Inputdate)
Debug.Print “Inputdate 1st ” & Inputdate
Debug.Print “HoldDate ” & HoldDate
Inputdate = DateAdd(“d”, 1, HoldDate)
Debug.Print “Inputdate 2nd ” & Inputdate
‘Loop Until Inputdate = EndDate
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Function
Public Function NextSunday(Inputdate As Date) As Date
‘ Returns the date of the first Sunday following the Inputdate
Select Case WeekDay(Inputdate, vbSunday)
Case 1
NextSunday = Inputdate
Case Else
NextSunday = CDate(Format(Inputdate + (7 – WeekDay(Inputdate) + 1), “mm/dd/yy”))
End Select
End Function
Data returned after running the function once.
Inputdate 1st 05/31/04
HoldDate 06/06/04
Inputdate 2nd 06/07/04
Data returned after running the function with the Do Loop.
The data returned is never the same.
HoldDate 08/18/46
Inputdate 2nd 08/19/46
Inputdate 1st 08/19/46
HoldDate 08/25/46
Inputdate 2nd 08/26/46
Inputdate 1st 08/26/46
