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

Posted on 2010-01-08
Last Modified: 2013-11-27
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.

Question by:Rex85
    LVL 92

    Accepted Solution

    The most recent Saturday is always:

    Date() - Weekday(Date())


    WHERE tblSaturdays.SaturdayDt <= (Date() - Weekday(Date()))
    LVL 26

    Expert Comment

    this will return the records relted to the current weeks records.
    SELECT *
      FROM yourTable

    Open in new window


    Author Comment

    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()))

    Author Closing Comment

    thanks. I cleared out all the parentheses it had built up and it worked. Thank you very much.

    LVL 8

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    Title # Comments Views Activity
    Calculate age in Access report 11 22
    Oracle Pivot 2 20
    Pl/SQL Query 31 35
    How have report show detail if a form checkbox is true 9 13
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now