Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Same Day Last year

Posted on 2007-07-23
Medium Priority
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

ID: 19551124

this takes care of leapyears

Author Comment

ID: 19551288
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

GRayL earned 1000 total points
ID: 19551313
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.
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

LVL 44

Expert Comment

ID: 19551380
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

ID: 19551769
Thanks, glad to help, but...
LVL 58

Expert Comment

ID: 19551845
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

ID: 37686857
Declare @ReturnDate datetime, @TheDate DateTime

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

SELECT @ReturnDate

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

577 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