Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting First and Last Day of Week

Posted on 2001-07-26
12
Medium Priority
?
320 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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