Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sunday Dates (Access 97)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sunday Dates (Access 97)

    1. You should test for Inputdate >= EndDate instead of Inputdate = EndDate. You can't be sure that InputDate will ever be exactly equal to EndDate:

    Loop Until InputDate >= EndDate

    2. Use this function to determine the next Sunday:

    Public Function NextSunday(Inputdate As Date) As Date
    ' Returns the date of the first Sunday following the Inputdate
    NextSunday = Inputdate + 7 - Weekday(Inputdate, vbMonday)
    End Function

    It's not only shorter, but it will correctly work on non-US systems, unlike your version.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sunday Dates (Access 97)

    Hans, thanks again, and again, and again.

    Adding the "Loop Until InputDate >= EndDate" and your function did the trick. I'm still confused as to why the function without >= didn?t start with the beginning date and run past the ending date instead of returning results all over the place? I might have had a clue if it had.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sunday Dates (Access 97)

    I'm not sure - on my Dutch system your version of the function didn't return the next Sunday correctly because of the date setting (mm-dd-yyyy), so I immediately modified it. I haven't tested the original version under US date settings.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •