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
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
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
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
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
(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
ASKER
Thank You I will try this tonight and let you know.
Ross
Ross
>((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
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
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
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
>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
>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
ASKER
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
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
ASKER
I have gotten it so it only asks me for the vbMonday parameter.
Here is the expresion:
Date()-Weekday(Now(),[vbMo
I am using the expresion builder is that Ok?
Ross
Here is the expresion:
Date()-Weekday(Now(),[vbMo
I am using the expresion builder is that Ok?
Ross
ASKER
expresion should read
Date()-Weekday(Now(),[vbMo
Date()-Weekday(Now(),[vbMo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You,
Ross
Ross
ASKER