Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: How to get this week's beginning and ending dates ...

    Hi all ...

    Thought I'd throw this one out there ... Anyone think of any improvements?

    - Mike

    Code:
         Dim StDate As Date, EndDate As Date
        Dim DayOfWeek As Integer, DateString As String
    
      DayOfWeek = DatePart("w", Date)
        CurrentWeek = DateAdd("d", (DayOfWeek - 2) * (-1), Date)
        EndDate = DateAdd("d", 6, CurrentWeek)
        If DatePart("m", CurrentWeek) = DatePart("m", EndDate) Then
            DateString = Format(CurrentWeek, "mmmm d") & " - " & DatePart("d", EndDate)
        Else
            DateString = Format(CurrentWeek, "mmm d") & " - " & Format(EndDate, "mmm d")
        End If
        Week1Lbl.Caption = DateString

    This yields: June 14 - 20
    Back to Access ... ADO is not the way to go for speed ...

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,419
    Provided Answers: 7
    I use the code for all my date reporting everything get taken to the next saturday

    Code:
    Function WEEKEND(dat) As Date
    If IsNull(dat) Then Exit Function
    dat = DateSerial(year(dat), Month(dat), Day(dat))
    If dat Mod 7 > 0 Then
    WEEKEND = dat - dat Mod 7 + 7
    Else
    WEEKEND = dat
    End If
    End Function
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by myle
    I use the code for all my date reporting everything get taken to the next saturday

    Code:
    Function WEEKEND(dat) As Date
    If IsNull(dat) Then Exit Function
    dat = DateSerial(year(dat), Month(dat), Day(dat))
    If dat Mod 7 > 0 Then
    WEEKEND = dat - dat Mod 7 + 7
    Else
    WEEKEND = dat
    End If
    End Function
    Thanks for the code snippet ... Who knows ... Some programming kiddie can use both our routines.
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Another approach:

    Code:
    Public Function GetWeekEnds(DateIn As Date, OutType As Integer) As Date
    Dim DayOfWeek As Integer
    DayOfWeek = DatePart("w", DateIn)
    If OutType = 0 Then
        GetWeekEnds = DateAdd("d", (7 - DayOfWeek), DateIn)
    Else
        GetWeekEnds = DateAdd("d", (7 - DayOfWeek) - 6, DateIn)
    End If
    End Function
    Usage:

    Beginning of week: GetWeekEnds(Date, 0)
    End of Week: GetWeekEnds(Date, 1)

    I'm lazy so I didn't put error checking on the toggle bit, but whatever.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,419
    Provided Answers: 7
    M owen

    Here a other one I Use

    Code:
    Function MY_YEAR(F As Date)
    Dim f1 As Integer
    Dim f2 As Integer
    If IsNull(F) Then Exit Function
    f1 = year(F)
    f2 = Month(F)
    f3 = 3  '<= start of the year
    If f2 > f3 Then   
    MY_YEAR = Right(Str(f1 + 0), 2) + "/" + Right(Str(f1 + 1), 2)
    Else
    MY_YEAR = Right(Str(f1 - 1), 2) + "/" + Right(Str(f1 + 0), 2)
    End If
    End Function
    Use it for report What Working Year is it
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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