SQL WHERE clause question

  • Thread starter Thread starter tomislav
  • Start date Start date
T

tomislav

hi

I want to pass WHERE clause to the Query:
"all records from last Sunday till last Friday"
or
"all records in last 7 days"

How I can do it, something like this:
WHERE StartDate = DateValue('???') AND EndDate = DateValue ('???')


I have fields:
Startdate
EndDate

cheers
tomislav


cheers
tomislav

--
**********************
Tomislav D. Perica
Resident Senior Program Officer
NDI Iraq/Pol. Party Program
MENA, Iraq, Baghdad
skype - tperica
gsm:+9647902646799
tel: +16014880349
(e-mail address removed)
http://www.ndi.org
 
You'll have to give a little more detail. What qualifies as "all records in
the last 7 days": those whose StartDate is in that range, those whose
EndDate is in that range, or either?

Also, what's your definition of "last Sunday" and "last Friday"? Today's
Sunday (Jan 22). I'm assuming "last Sunday" would be Jan 15th, and "last
Friday" would be Jan 20, but what dates would you have wanted last Thursday
(Jan 19)?

In general, you can create a range of "the last 7 days" as Between
DateAdd(-7, d, Date) And Date.

To figure out for specific weekdays, you need to use the Weekday function to
figure out what day of the week today is, and use that in your calculation
of how many days to subtract from Date.

I don't have a specific formula handy, but take a look at my September, 2004
and June, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access" for examples of how to include the weekday in your calculations. You
can download the columns (and sample databases) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html
 
Hi Douglas, In the criteria the DateAdd should be

Between DateAdd("d",-7,Date) And Date.
 
Yup, you're absolutely right.

Thanks for spotting that.
Click to expand...

Hi guys. Thanks for helping me. I tried, but it's not working. Let me
explain one more time:

I have one table and two fileds for date: "start date" and "end date".
Medium Date (01-Oct-2005)


I want to create Q and then Report, so whenever user clicks, he/she will
get list of the trainings in last 7 days, counting from "click day"

or similar thing:

list of the trainings form last sunday to sunday before last sunday


so SQL will be: "WHERE Between ..."

cheers
tomislav

--
**********************
Tomislav D. Perica
Resident Senior Program Officer
NDI Iraq/Pol. Party Program
MENA, Iraq, Baghdad
skype - tperica
gsm:+9647902646799
tel: +16014880349
(e-mail address removed)
http://www.ndi.org
 
tomislav said:
Hi guys. Thanks for helping me. I tried, but it's not working. Let me
explain one more time:

I have one table and two fileds for date: "start date" and "end date".
Medium Date (01-Oct-2005)


I want to create Q and then Report, so whenever user clicks, he/she will
get list of the trainings in last 7 days, counting from "click day"

or similar thing:

list of the trainings form last sunday to sunday before last sunday


so SQL will be: "WHERE Between ..."
Click to expand...

You still haven't answered the question I asked. What qualifies as "all
records in the last 7 days": those whose StartDate is in that range, those
whose EndDate is in that range, where both StartDate and EndDate are in that
range, or something else?

If anything that started in the last 7 days, you'd use

WHERE [Start Date] BETWEEN DateAdd("d", -7, Date) AND Date

If anything that ended in the last 7 days, you'd use

WHERE [End Date] BETWEEN DateAdd("d", -7, Date) AND Date

You can calculate "last sunday" using DateAdd("d", 1 - Weekday(Date), Date),
which means that the previous Sunday would be DateAdd("d", 8 -
Weekday(Date), Date). Plug those in the WHERE clause above rather than the
two values I indicated.
 
You must log in or register to reply here.
Back
Top