Solved

Getting First and Last Day of Week

Posted on 2001-07-26
12
313 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

626 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