Solved

Getting First and Last Day of Week

Posted on 2001-07-26
12
310 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:bozo7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 1

Author Comment

by:bozo7
ID: 6323849
Oh Yeah I am using Access 2000
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6323885
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
 
LVL 8

Expert Comment

by:dovholuk
ID: 6323890
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 1

Author Comment

by:bozo7
ID: 6323910
Thank You I will try this tonight and let you know.
Ross
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6324084
>((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
 
LVL 8

Expert Comment

by:dovholuk
ID: 6324471
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
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6324616
>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
 
LVL 1

Author Comment

by:bozo7
ID: 6325461
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
 
LVL 1

Author Comment

by:bozo7
ID: 6325466
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
 
LVL 1

Author Comment

by:bozo7
ID: 6325469
expresion should read
Date()-Weekday(Now(),[vbMonday]) + 1
0
 
LVL 8

Accepted Solution

by:
dovholuk earned 200 total points
ID: 6325513
vbmonday is an access constant. replace it with 2, ( 2 = vbmonday, 1 = vbsunday, etc.) the literal value for vbMonday.

dovholuk

0
 
LVL 1

Author Comment

by:bozo7
ID: 6326655
Thank You,

Ross
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question