Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number of Week based on Date (Access2000 SR-1)

    Hi.
    I have a date field and I need to create a query that will tell me what number of the week it is relative to the date and month, staring with Monday to Friday.

    Basically I have a little calendar program and I need to setup a report that will print out the calendar based on the week of that month:

    Week 01
    Mon....
    Tues....
    Wed...
    Thurs....
    Friday....

    (End of page 1)

    Once this is accomplished I can do a monthly report and have it print out in one week intervals

    Thanks

    Paul

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Number of Week based on Date (Access2000 SR-1)

    Try the following in your query.

    WeekNumber: Int(Day([DATE])/7+1)

    HTH
    Regards,

    Gary
    (It's been a while!)

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

    Re: Number of Week based on Date (Access2000 SR-1)

    Paul,

    What is week 1 in a month? The week that starts with the first Sunday in the month, or with the first Monday in the month, or the first week Sun - Sat of which at least 4 days fall within that month?

    This probably seems silly to you, but it is a serious question. Different countries have different ways of determining such things; yours may have a different "rule" for it than mine.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of Week based on Date (Access2000 SR-1)

    I AM CHANGING MY REQUIREMENTS A LITTLE.

    This is a little tricky to explain, but I can see something formulating in my head.
    I want to report upcomming events for the next 3 weeks. So this will be a floating function.
    Currently I have a Parameter Query that asks the person for the Monday Date and the Friday Date of the range they want to report on.

    I want to indentify the M-F for the next three weeks. That way I can print a calendar of events for the next three weeks, ONE week at a time. If I can do this, I can create 3 identical reports that can be printed out individually as the user wants.

    Scenario:
    Today is Friday April 25. I have a [Date] field to work with as well.

    Report1: Calendar for the Week of 4/28/2003 or [Calculating from TODAY, what is the NEXT MONDAY and capture only dates for Monday to Friday]

    Report2: Calendar for the Week of 5/5/2003 or [Calculating from TODAY, what is the NEXT MONDAY + 7 DAYS TO FOLLOWING MONDAY and capture only dates for Monday to Friday]

    Report1: Calendar for the Week of 5/12/2003 or [Calculating from TODAY, what is the NEXT MONDAY + 14 DAYS TO FOLLOWING MONDAY and capture only dates for Monday to Friday]

    Thanks
    Paul

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of Week based on Date (Access2000 SR-1)

    FURTHER Update:

    I am playing around with this function:

    Week: Date()+[Number of days till next monday]

    If I can MARK each Weekday with a number: Su=1 M=2 T=3 W=4 Tu=5 F=6 Sa=7

    The I can make this formula: TODAY = 6 and MONDAY = 2 therefore there are 3 days until Monday

    or: Week: Date()+3m = 4/28/2003


    Maybe I need to get drunk?

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

    Re: Number of Week based on Date (Access2000 SR-1)

    The first Monday from today is Date() + 7 - Weekday(Date(), 3). Note: if "today" is a Monday, this will return "today", not the Monday a week from "today". If you prefer the latter, use Date() + 8 - Weekday(Date(), 2) instead.

    So you can use the following criteria for the Date field in your query:

    Report 1: Between Date()+7-Weekday(Date(),3) And Date()+11-Weekday(Date(),3)
    Report2: Between Date()+14-Weekday(Date(),3) And Date()+18-Weekday(Date(),3)
    Report3: Between Date()+21-Weekday(Date(),3) And Date()+25-Weekday(Date(),3)

    or, if you prefer the Monday a week from today if today is a Monday, use these

    Report 1: Between Date()+8-Weekday(Date(),2) And Date()+12-Weekday(Date(),2)
    Report2: Between Date()+15-Weekday(Date(),2) And Date()+19-Weekday(Date(),2)
    Report3: Between Date()+22-Weekday(Date(),2) And Date()+26-Weekday(Date(),2)

    Note: in code, you would use vbTuesday instead of 3, and vbMonday instead of 2 in the Weekday function, but you can't use these symbolic constants in a query.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of Week based on Date (Access2000 SR-1)

    LAST TIME:

    I FIGURED OUT WHAT I NEED:

    1. TODAY + 8 days = Next friday (5/2/2003) This will always give me a date somewhere within the next WorkWeek.

    2. I need a function that will return BASED on 5/2/2003, the date range for M - F ( 4/28/2003 - 5/2/2003) This will be my range for the 1st week report.

    3. I also need a function to just return the Monday of the week being calculated (5/2/2003, based on query 1, above) = 4/28/2003

    IF I GET THIS:, then I can just modify the 1st query to TODAY + 16 days 2 weeks out |and| TODAY + 24 days for 3 weeks out.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Number of Week based on Date (Access2000 SR-1)

    Don't get drunk (unless you really want to <img src=/S/grin.gif border=0 alt=grin width=15 height=15>), just use Hans clever use of the Weekday function.

    Pat

Posting Permissions

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