Getting First and Last Day of Week

I am creating a report where the user wants it to print the records for the current week. They asked for a similiar report except for just the current date for that one I used Date() in the query.
I would like to do something similiar in the query except getting the first and last day of the week something like

DateField1 between FirstDate and LastDate


Thanks
Ross
LVL 1
bozo7Asked:
Who is Participating?
 
dovholukCommented:
vbmonday is an access constant. replace it with 2, ( 2 = vbmonday, 1 = vbsunday, etc.) the literal value for vbMonday.

dovholuk

0
 
bozo7Author Commented:
Oh Yeah I am using Access 2000
0
 
dovholukCommented:
use the following:
first day of the week (Monday) would be: date - weekday(now, vbMonday) + 1
last day of week (Friday): (6 - weekday(now, vbMonday)) + date

use those in your between statement (not sure on the between syntax):
datefield1 between (date - weekday(now, vbMonday) + 1) and ((6 - weekday(now, vbMonday)) + date)

dovholuk
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dovholukCommented:
date - weekday(now, vbMonday) + 1 produces a result of 7/23/01 (monday july 23rd)
(6 - weekday(now, vbMonday)) + date yields a result of 7/28/01 (friday, july 28th)

depending on when you want your week to start and end, you can change the vbMonday in the first expression, or the 6 in the second expression.

need more help?

dovholuk
0
 
bozo7Author Commented:
Thank You I will try this tonight and let you know.
Ross
0
 
DennisBorgCommented:
>((6 - weekday(now, vbMonday)) + date)

This seems to overshoot Friday by one day and actually targets Saturday. (July 28 is Saturday, not Friday)

Personally, I find it the following more simple:

   Monday = Date - Weekday(Date, vbMonday) + 1
   Friday = Date - Weekday(Date, vbMonday) + 5


-Dennis Borg
P.S. This is just an assist for Ross
0
 
dovholukCommented:
i THOUGHT + 6 was silly! thanks for clarifying saturday is the 28th, not friday! duh for me...

as for your "more simple" code... it's the exact same thing i posted above (except of course that i should have used 5 and not 6 lol) just arranged differently...
a-b+c still equals -b+a+c still equals a+c-b etc. it just depends on how you're looking at it. ;)

dovholuk

0
 
DennisBorgCommented:
>i THOUGHT + 6 was silly! thanks for clarifying saturday
>is the 28th, not friday! duh for me...

That's alright. I just realized that I was thinking today was Friday and that it's only Thursday. Wishful thinking, I guess!



>as for your "more simple" code... it's the exact same
>thing i posted above (except of course that i
>should have used 5 and not 6 lol) just arranged
>differently...
>a-b+c still equals -b+a+c still equals a+c-b etc. it just
>depends on how you're looking at it. ;)

Yes, you are correct. I guess I should have said "more intuitive" than "more simple". I had to look at it a couple times to figure it out; but the second look on my part is probably largely due to moving myself, helping a friend load the truck for a move, and a couple dozen other irons in the fire. I'm looking forward to the weekend to catch up on some much-needed sleep!


-Dennis Borg
0
 
bozo7Author Commented:
When I put the following in the expression to see what value it returns the query prompts me for the Date Parameter and the the vbMonday parameter.

date - weekday(now, vbMonday) + 1

What do I have wrong?

I can put this expresion in the field expresion and it should work for testing right? Even when I put in the Criteria section it prompts me for the parameters.
I will keep trying to figure it out.

Thanks,
Ross
0
 
bozo7Author Commented:
I have gotten it so it only asks me for the vbMonday parameter.
Here is the expresion:
 Date()-Weekday(Now(),[vbMonday]+1)

I am using the expresion builder is that Ok?
Ross
0
 
bozo7Author Commented:
expresion should read
Date()-Weekday(Now(),[vbMonday]) + 1
0
 
bozo7Author Commented:
Thank You,

Ross
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.