By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

More Date Issues

P: 33
Hi,

I'm trying to remove the need for someone to enter a date range for queries because the dates will always be the Sunday through Saturday of the previous week.

Can someone please help me with the "Where" statement I need to put in the queries that will automatically set those dates?

Thanks!
May 15 '08 #1
Share this Question
Share on Google+
4 Replies


jeffstl
Expert 100+
P: 432
Hi,

I'm trying to remove the need for someone to enter a date range for queries because the dates will always be the Sunday through Saturday of the previous week.

Can someone please help me with the "Where" statement I need to put in the queries that will automatically set those dates?

Thanks!
So basically you need to write a script that generates 2 dates detecting automatically the dates matching your description of a previous week?

Not easily done. If you really want to do it then try writing a script using the VB Date function that will generate these dates, post problems as you have them.

Another thing you could try is a calander control so the users can input dates easier on a form or something.

Lastly I will say that the ability to enter the date range rather then the program automatically doing it actually leaves some flexibility in your application. What if those dates change? What if you want a larger report from a larger range?

Seems a small price to pay for users to have to enter 2 dates into a parameter box. Especially if its only once per week.
May 15 '08 #2

P: 33
Thanks jeffstl! I'll try the VB Date function.

I've used the entry form in this application for a while now but I've bought a new automation tool that can run this macro while I'm at home in bed, produce the output in Excel and automatically email the Excel file as an attachment to my distribution list. So, there is some benefit to trying to automate it.

Thanks again!
May 15 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
Sunday of the previous week
(Date - Weekday(Date) + 1) - 7

Saturday of the previous week
Date - Weekday(Date)

Welcome to Bytes!

Linq ;0)>
May 15 '08 #4

jeffstl
Expert 100+
P: 432
Sunday of the previous week
(Date - Weekday(Date) + 1) - 7

Saturday of the previous week
Date - Weekday(Date)

Welcome to Bytes!

Linq ;0)>
Good! There you go OP.

I figured there was a way but I didn't have it in the front of my mind.

You can take the value obtained and pass them to your SQL. I assume since your query already accepted 2 parameters that its ready to accept these calculated values.
May 15 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.