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

Calculating seven days from a Start Date

P: n/a
Del
I have a parameter query that requires the user to enter a Start Date:
and End Date: and pull data between that date range. I am currently
using the following parameter;

Select *
From mytable
Where [SomeDate] between [start date:] and [end date:]

This query will always be run on a Monday and the start date will
always be the previous Sunday. The end date will be the previous
Monday. So I would like to set the query up so the user would only
have to enter the Start Date and the query would calculate 6 days back
for Monday's date and pull the data for that range.

I have tried:

Select *
From mytable
Where [SomeDate] between Between DateAdd("d",-6,[Start Date:]) And
[Start Date:]

When I run this query it starts with the Start Date but shows all data
past the wanted ending date.
Example: if mytable contains information from 01/01/2008 through
02/12/2008 and I enter the Start Date as 02/10/2008 the query results
are from 02/10/2008 and 01/01/2008

How do I get the query to only show data from 02/10/2008 back to
02/04/2008

Thank you!

Feb 12 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Del wrote:
I have a parameter query that requires the user to enter a Start Date:
and End Date: and pull data between that date range. I am currently
using the following parameter;

Select *
From mytable
Where [SomeDate] between [start date:] and [end date:]

This query will always be run on a Monday and the start date will
always be the previous Sunday. The end date will be the previous
Monday. So I would like to set the query up so the user would only
have to enter the Start Date and the query would calculate 6 days back
for Monday's date and pull the data for that range.

I have tried:

Select *
From mytable
Where [SomeDate] between Between DateAdd("d",-6,[Start Date:]) And
[Start Date:]

When I run this query it starts with the Start Date but shows all data
past the wanted ending date.
Example: if mytable contains information from 01/01/2008 through
02/12/2008 and I enter the Start Date as 02/10/2008 the query results
are from 02/10/2008 and 01/01/2008

How do I get the query to only show data from 02/10/2008 back to
02/04/2008

Thank you!
Try this
Between
DateSerial(Year([EnterDate]),Month([EnterDate]),Day([EnterDate])-6) And
DateSerial(Year([EnterDate]),Month([EnterDate]),Day([EnterDate]))

50 Feet Of Insanity
http://www.youtube.com/watch?v=8LoH6oQzJZs
Feb 12 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.