shane_leone
asked on
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()),
Last year:>=DateSerial(Year(Dat
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
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()),
Last year:>=DateSerial(Year(Dat
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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?
(°v°)
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°)
Declare @ReturnDate datetime, @TheDate DateTime
SET @TheDate = '08/06/2012'
SET @ReturnDate =DATEADD(DayOfYear,-364,@T
SELECT @ReturnDate
SET @TheDate = '08/06/2012'
SET @ReturnDate =DATEADD(DayOfYear,-364,@T
SELECT @ReturnDate
this takes care of leapyears