More Date Issues

[Shawn29316]

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!

[jeffstl]

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.

[Shawn29316]

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!

[missinglinq]

Sunday of the previous week
(Date - Weekday(Date) + 1) - 7

Saturday of the previous week
Date - Weekday(Date)

Welcome to Bytes!

Linq ;0)>

[jeffstl]

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.