[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

How Can I Create a WHERE Statement that Always Cuts the Data Off at Last Week

I have a Query that is an Outer Join of a table of possible week ending dates (Saturdays) and tables of production data grouped by week ending dates (Saturdays)

I use this approach to accomodate possible "down" weeks. Rather than skip the week, it will return a null for that week, but since all plants are included, something is always returned (Never are all plants down at once)

the problem is this potential table goes out in the future several years. For intenral reasons, the current WHERE only pulls data with a weekendingdate greater than 4/12/2008.

I would like it to cut off the data at the previous week, (Most recent past Saturday) but I don't know how to construct that.

Has anyone ever done this, and can you show me how? Thanks.

All the dates have already been converted to Saturday week ending dates, so the WHERE only has to limit the data be the most recent past Saturday at any time the query is run.

1 Solution
Patrick MatthewsCommented:
The most recent Saturday is always:

Date() - Weekday(Date())


WHERE tblSaturdays.SaturdayDt <= (Date() - Weekday(Date()))
this will return the records relted to the current weeks records.
  FROM yourTable

Open in new window

Rex85Author Commented:
At the Risk of Being a PIA, I keep getting a Syntax Error on stringing that togethr with the code you gave me on >#4/12/2008. It says missing operator, but I can't figure out where. nothing works.

How would I write the WHERE tblSaturdays.SaturdayDt > #4/1/2008# AND tblSaturdays.SaturdayDt <= (Date() - Weekday(Date()))
Rex85Author Commented:
thanks. I cleared out all the parentheses it had built up and it worked. Thank you very much.

in case you still don't have what you asked in that last post ...

in sql server, you would enclose the date in single quotes, as '4/1/2008'.  that may be all you need, but not being an access user, i can't say.

also, i am a big fan of using date functions (datediff, dateadd, etc) instead of simple comparisons and native math on dates.  it can keep you out of trouble when you run into unexpected date formats or values, nulls, etc.

so for example the first part of the where clause would be
where datediff(d, '4/1/2008', tblSaturdays.SaturdayDt) > 0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now