Same Day Last year

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
Who is Participating?
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.

this takes care of leapyears
shane_leoneAuthor Commented:
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.
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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.
Thanks, glad to help, but...
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?
Declare @ReturnDate datetime, @TheDate DateTime

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

SELECT @ReturnDate
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.