Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Set date to week of the month

Discussion in 'Information Technology' started by Lynn, Oct 27, 2005.

Tags:
  1. Lynn

    Lynn
    Expand Collapse
    Guest

    I am trying to creat a button on a form that will pull data according to the
    week of the month. I want it to count week 1, 2, 3, 4, 5 (if there are 5
    weeks within a certain month) and then start over with week 1 the next month.
    The new month will always begin on the first Monday. Example: 10/3 - 10/9
    would be week 1, 10/10 - 10/16 would be week 2, 10/17 - 10/23 would be week
    3, 10/24 - 10/30 would be week 4 and 10/31 - 11/6 would be week 5. The new
    month would begin on 11/7 as week 1. Is there a way to assign weeks in
    Access? Thanks!
     
  2. Loading...

    Similar Threads Forum Date
    1984 US court sets date for pre-trial hearing in 1984 riots case History of Sikhism May 31, 2011
    Sikh News Candidate talks of her assets (The Daily Review) Breaking News Sep 10, 2006
    Sikh News Sikh candidate in Pak local polls hopes to set record (Outlook India) Breaking News Aug 22, 2005
    Sikh News Settlement Leads To Peaceful Transition At Sikh Temple Breaking News Sep 10, 2016
    Sikhi Langar-Aid - Sikhs Have Set Up A 'Langar' In The ISIS Territory of Syria To Feed Refugees Sikh Sikhi Sikhism Oct 31, 2015

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Use a query to buils a list of Mondays. If Monday is a holiday then you will
    need to add a No Sale date record.

    Query named SalesWeek --
    SELECT Weekday([SalesDate]) AS x, Format([SalesDate],"mm/dd") AS SalesWeek
    FROM Sales
    WHERE (((Weekday([SalesDate]))=2));

    Total Sales for Week --
    SELECT SalesWeek.SalesWeek, Sum(Sales.QTY) AS SumOfQTY
    FROM Sales, SalesWeek
    WHERE (((DateDiff("d",[SalesDate],[SalesWeek])) Between 1 And 7))
    GROUP BY SalesWeek.SalesWeek;


    "Lynn" wrote:

    > I am trying to creat a button on a form that will pull data according to the
    > week of the month. I want it to count week 1, 2, 3, 4, 5 (if there are 5
    > weeks within a certain month) and then start over with week 1 the next month.
    > The new month will always begin on the first Monday. Example: 10/3 - 10/9
    > would be week 1, 10/10 - 10/16 would be week 2, 10/17 - 10/23 would be week
    > 3, 10/24 - 10/30 would be week 4 and 10/31 - 11/6 would be week 5. The new
    > month would begin on 11/7 as week 1. Is there a way to assign weeks in
    > Access? Thanks!
     
  4. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    You will need to create you own function to calculate this. You could then
    pass the current date to that function and have the function return the week
    number.

    I did a few quick tests, but I think this will do it.

    Public Function WeekOfMonth2(dteDate As Date) As Integer
    'Count Monday as the first day of the week.
    'Days in the month before the first Monday
    'are treated as belonging to the last week
    'of the previous month.
    Dim intWeekDay As Integer, intDay As Integer
    Dim i As Integer, intFirstMonday As Integer
    intWeekDay = Weekday(dteDate)
    intDay = Day(dteDate)
    'If the day of the month is before the first Monday
    'Then get the week of the last day of the previous month
    If intWeekDay <> 2 And intDay < 7 Then
    WeekOfMonth2 = WeekOfMonth2(DateSerial(Year(dteDate), Month(dteDate),
    0))
    Else
    'Find the first Monday
    For i = 1 To 7
    If Weekday(DateSerial(Year(dteDate), Month(dteDate), i)) = 2 Then
    intFirstMonday = i
    Exit For
    End If
    Next i
    WeekOfMonth2 = ((intDay - intFirstMonday) \ 7) + 1
    End If
    End Function

    --
    Wayne Morgan
    MS Access MVP


    "Lynn" <Lynn@discussions.microsoft.com> wrote in message
    news:5A4F78B0-1710-49BA-BDDB-7BB77C48E0B3@microsoft.com...
    >I am trying to creat a button on a form that will pull data according to
    >the
    > week of the month. I want it to count week 1, 2, 3, 4, 5 (if there are 5
    > weeks within a certain month) and then start over with week 1 the next
    > month.
    > The new month will always begin on the first Monday. Example: 10/3 -
    > 10/9
    > would be week 1, 10/10 - 10/16 would be week 2, 10/17 - 10/23 would be
    > week
    > 3, 10/24 - 10/30 would be week 4 and 10/31 - 11/6 would be week 5. The
    > new
    > month would begin on 11/7 as week 1. Is there a way to assign weeks in
    > Access? Thanks!
     
  5. Lynn

    Lynn
    Expand Collapse
    Guest

    This code worked fine but now I have a change to make. If the first week of
    the month does not fall on a monday I still want that week to be week 1 and
    then each monday after that will be an additional week. For example.....in
    November the 1st is on a tuesday, I want that to be week 1 and then on monday
    11/7 to be week 2, etc. How do I change the code to do that?

    "Wayne Morgan" wrote:

    > You will need to create you own function to calculate this. You could then
    > pass the current date to that function and have the function return the week
    > number.
    >
    > I did a few quick tests, but I think this will do it.
    >
    > Public Function WeekOfMonth2(dteDate As Date) As Integer
    > 'Count Monday as the first day of the week.
    > 'Days in the month before the first Monday
    > 'are treated as belonging to the last week
    > 'of the previous month.
    > Dim intWeekDay As Integer, intDay As Integer
    > Dim i As Integer, intFirstMonday As Integer
    > intWeekDay = Weekday(dteDate)
    > intDay = Day(dteDate)
    > 'If the day of the month is before the first Monday
    > 'Then get the week of the last day of the previous month
    > If intWeekDay <> 2 And intDay < 7 Then
    > WeekOfMonth2 = WeekOfMonth2(DateSerial(Year(dteDate), Month(dteDate),
    > 0))
    > Else
    > 'Find the first Monday
    > For i = 1 To 7
    > If Weekday(DateSerial(Year(dteDate), Month(dteDate), i)) = 2 Then
    > intFirstMonday = i
    > Exit For
    > End If
    > Next i
    > WeekOfMonth2 = ((intDay - intFirstMonday) \ 7) + 1
    > End If
    > End Function
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "Lynn" <Lynn@discussions.microsoft.com> wrote in message
    > news:5A4F78B0-1710-49BA-BDDB-7BB77C48E0B3@microsoft.com...
    > >I am trying to creat a button on a form that will pull data according to
    > >the
    > > week of the month. I want it to count week 1, 2, 3, 4, 5 (if there are 5
    > > weeks within a certain month) and then start over with week 1 the next
    > > month.
    > > The new month will always begin on the first Monday. Example: 10/3 -
    > > 10/9
    > > would be week 1, 10/10 - 10/16 would be week 2, 10/17 - 10/23 would be
    > > week
    > > 3, 10/24 - 10/30 would be week 4 and 10/31 - 11/6 would be week 5. The
    > > new
    > > month would begin on 11/7 as week 1. Is there a way to assign weeks in
    > > Access? Thanks!

    >
    >
    >
     
  6. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    This one is a little simpler.

    Public Function WeekOfMonth(dteInputDate As Date) As Integer
    Dim intDate As Integer
    intDate = Day(dteInputDate)
    WeekOfMonth = (intDate \ 7) + 1 + (intDate Mod 7 = 0)
    End Function


    --
    Wayne Morgan
    MS Access MVP


    "Lynn" <Lynn@discussions.microsoft.com> wrote in message
    news:3EB7DD12-8C65-4E87-9ED0-5507431EE3D1@microsoft.com...
    > This code worked fine but now I have a change to make. If the first week
    > of
    > the month does not fall on a monday I still want that week to be week 1
    > and
    > then each monday after that will be an additional week. For
    > example.....in
    > November the 1st is on a tuesday, I want that to be week 1 and then on
    > monday
    > 11/7 to be week 2, etc. How do I change the code to do that?
    >
    > "Wayne Morgan" wrote:
    >
    >> You will need to create you own function to calculate this. You could
    >> then
    >> pass the current date to that function and have the function return the
    >> week
    >> number.
    >>
    >> I did a few quick tests, but I think this will do it.
    >>
    >> Public Function WeekOfMonth2(dteDate As Date) As Integer
    >> 'Count Monday as the first day of the week.
    >> 'Days in the month before the first Monday
    >> 'are treated as belonging to the last week
    >> 'of the previous month.
    >> Dim intWeekDay As Integer, intDay As Integer
    >> Dim i As Integer, intFirstMonday As Integer
    >> intWeekDay = Weekday(dteDate)
    >> intDay = Day(dteDate)
    >> 'If the day of the month is before the first Monday
    >> 'Then get the week of the last day of the previous month
    >> If intWeekDay <> 2 And intDay < 7 Then
    >> WeekOfMonth2 = WeekOfMonth2(DateSerial(Year(dteDate), Month(dteDate),
    >> 0))
    >> Else
    >> 'Find the first Monday
    >> For i = 1 To 7
    >> If Weekday(DateSerial(Year(dteDate), Month(dteDate), i)) = 2 Then
    >> intFirstMonday = i
    >> Exit For
    >> End If
    >> Next i
    >> WeekOfMonth2 = ((intDay - intFirstMonday) \ 7) + 1
    >> End If
    >> End Function
    >>
    >> --
    >> Wayne Morgan
    >> MS Access MVP
    >>
    >>
    >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message
    >> news:5A4F78B0-1710-49BA-BDDB-7BB77C48E0B3@microsoft.com...
    >> >I am trying to creat a button on a form that will pull data according to
    >> >the
    >> > week of the month. I want it to count week 1, 2, 3, 4, 5 (if there are
    >> > 5
    >> > weeks within a certain month) and then start over with week 1 the next
    >> > month.
    >> > The new month will always begin on the first Monday. Example: 10/3 -
    >> > 10/9
    >> > would be week 1, 10/10 - 10/16 would be week 2, 10/17 - 10/23 would be
    >> > week
    >> > 3, 10/24 - 10/30 would be week 4 and 10/31 - 11/6 would be week 5. The
    >> > new
    >> > month would begin on 11/7 as week 1. Is there a way to assign weeks in
    >> > Access? Thanks!

    >>
    >>
    >>
     
  7. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Wayne Morgan wrote:
    > This one is a little simpler.
    >
    > Public Function WeekOfMonth(dteInputDate As Date) As Integer
    > Dim intDate As Integer
    > intDate = Day(dteInputDate)
    > WeekOfMonth = (intDate \ 7) + 1 + (intDate Mod 7 = 0)
    > End Function
    >
    >


    Here's a more general version:

    Public Function WeekOfMonth(dteInputDate As Date, intStartWeekday As
    Integer) As Integer
    Dim intWeekdayOf1st As Integer
    Dim intDaysInWeek1 As Integer
    Dim intDaysPastWeek1 As Integer

    intWeekdayOf1st = WeekDay(DateSerial(Year(dteInputDate),
    Month(dteInputDate), 1))
    intDaysInWeek1 = (6 + intStartWeekday - intWeekdayOf1st) Mod 7 + 1
    If Day(dteInputDate) <= intDaysInWeek1 Then
    WeekOfMonth = 1
    Else
    intDaysPastWeek1 = Day(dteInputDate) - intDaysInWeek1
    WeekOfMonth = 1 + (intDaysPastWeek1 \ 7) + Abs(intDaysPastWeek1 Mod 7
    <> 0)
    End If
    End Function


    Note: This function can be shortened slightly by using the Ceiling(X) =
    - Int(-X) function posted by Van T. Dinh:
    WeekOfMonth = 1 - Int(-intDaysPastWeek1 / 7)
    When used in a query, use the weekday numbers (Sunday = 1, ..., Saturday
    = 7) directly instead of vbSunday, ..., vbSaturday. It counts the days
    during the month before the first starting weekday, if any, as week 1.

    Also, see the technique used in:

    http://groups.google.com/group/microsoft.public.access/msg/c777af0e1fa201c4

    Note: The Ceiling function can similarly simplify the expression used there.

    James A. Fortune
     

Share This Page