Same Day Last year

Posted on 2007-07-23
Last Modified: 2012-03-06
I'm putting together a retail comp report that compares the current day stats to the same day, previous year.  Is there a code I can use to get last year, same day?
For example:  This year stats= 7/22/2007-Sunday
                     Previous year=  7/23/2006-Sunday
I need to be able to put this string into the design view in a criteria field.  For Calendar comp I use the following.
                     This year: >=DateSerial(Year(Date()),Month(Date()),1) And <=Date()-1
                    Last year:>=DateSerial(Year(Date())-1,Month(Date()),1) And <=Date()-366

                     Can this be done for same day is what I'm wondering?  Any help would be greatly appreciative.

I'm currently running Windows XP Pro SP1 and MS Access 2000
Question by:shane_leone
    LVL 44

    Expert Comment


    this takes care of leapyears

    Author Comment

    If this year stats are on 7/23/2007-Monday
    I need to compare last year's equivalent Monday-7/24/2007.  What you gave me GRayL gives me the same calendar date 7/23/2006 which is fine but this day was a Sunday which doesn't help with what I need.
    LVL 44

    Accepted Solution

    What you have marked as 'This year:' will give you the current month to yesterday.
    'Last year:' will give you this month a year ago less a day.  Leap year will complicate this.  I presume you have a date field in a table where you want to extract these two periods. Try:

    This year:  fldDate Between Date()-Day(Date())+1 and Date()-1

    Last year:  fldDate Between DateSerial(Year(Date())-1,Month(Date(),1) and DateSerial(year(Date())-1,Month(Date()),Day(Date()-1)

    This should take care of leap years.
    LVL 44

    Expert Comment

    It is not clear what you want.  You talk about excluding yesterday because it was a Sunday.  What about all the other Sundays?

    Start with:

    In my table myTableName, I have a date field fldDate.  I want to include all the records for this month, excluding ....<add what you want to exclude>

    I want the same period for last year.
    LVL 44

    Expert Comment

    Thanks, glad to help, but...
    LVL 58

    Expert Comment

    If you want the closest date last year with the same weekday, you can simply use the multiple of 7 closest to 365.25, namely 7*52 = 364.

        Today: Date()
        LastYear: Date()-364

    You also wanted "this year" to be the start of the current month, or:

        ThisYear: Date() - Day(Date()) + 1
        LastYear: Date() - Day(Date()) - 363

    Something like that?

    Expert Comment

    Declare @ReturnDate datetime, @TheDate DateTime

          SET @TheDate = '08/06/2012'
          SET @ReturnDate =DATEADD(DayOfYear,-364,@TheDate)

    SELECT @ReturnDate

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    733 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

    20 Experts available now in Live!

    Get 1:1 Help Now