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
shane_leoneAsked:
Who is Participating?
 
GRayLCommented:
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.
0
 
GRayLCommented:
LastYear:DateAdd("yyyy",-1,Date())

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

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

 
GRayLCommented:
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.
0
 
GRayLCommented:
Thanks, glad to help, but...
0
 
harfangCommented:
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?
(°v°)
0
 
jsmetonaCommented:
Declare @ReturnDate datetime, @TheDate DateTime

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

SELECT @ReturnDate
0
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.