Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Getting First and Last Day of Week

Posted on 2001-07-26
12
Medium Priority
?
324 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
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 800 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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 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