469,584 Members | 2,440 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,584 developers. It's quick & easy.

Week of the month

Hi

I have a report which lists staff hours for various dates. What could be a
way to assign to each entry the week of the month that the date for the
entry belongs to?

Thanks

Regards
Nov 13 '05 #1
3 4803
Week of month defined as;

if date1 is date from the first entry whatever it is then
date1 - date1+6 = week 1
date1+7 - date1+13 = week 2

and so on..

Slightly tricky I can see, as date1 could be anything depending on the
report input query..

Regards

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:41***********************@news-text.dial.pipex.com...
Hi

I have a report which lists staff hours for various dates. What could be a
way to assign to each entry the week of the month that the date for the
entry belongs to?

Thanks

Regards

Nov 13 '05 #2
John wrote:
Week of month defined as;

if date1 is date from the first entry whatever it is then
date1 - date1+6 = week 1
date1+7 - date1+13 = week 2

and so on..

Slightly tricky I can see, as date1 could be anything depending on the
report input query..

Regards

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:41***********************@news-text.dial.pipex.com...
Hi

I have a report which lists staff hours for various dates. What could be a
way to assign to each entry the week of the month that the date for the
entry belongs to?

Thanks

Regards


Here I use 10/1/2004 as the start date. I subtract the days between
start/end, divide by 7 to get the week, and add 1 to get the week so
that the number of the week starts at 1, not 0.

'week where date is same as start day
? datediff("d",#10/1/2004#,#10/1/2004#+7)\7 + 1
1
'week 7 days out
? datediff("d",#10/1/2004#,#10/1/2004#+7)\7 + 1
2

Nov 13 '05 #3
"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:41***********************@news-text.dial.pipex.com...
Hi

I have a report which lists staff hours for various dates. What could be a
way to assign to each entry the week of the month that the date for the
entry belongs to?

Thanks

Regards


Create a table to hold all your distinct time periods - in this case weeks.
Label each row with the correct week number and join to the new table in
your queries.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by androtech | last post: by
10 posts views Thread by Ty Smith via AccessMonster.com | last post: by
6 posts views Thread by aarklon | last post: by
2 posts views Thread by TGEAR | last post: by
10 posts views Thread by Jim | last post: by
3 posts views Thread by =?Utf-8?B?cm9kY2hhcg==?= | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.