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

Topic

New Reply

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

Reply | Quote

Replies

Try the following in your query.

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

HTH

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Don’t get drunk (unless you really want to ), just use Hans clever use of the Weekday function.

Pat

Reply | Quote

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.

Reply | Quote