Solved

Need SQL code to query month to date last year

Posted on 2014-10-29
4
625 Views
Last Modified: 2014-10-30
I am trying to build a query in Access 2010 to pull data from month to date last year.

Example. today being 10/29/14, I am using Between DateAdd("d",-DatePart("d",Date())+1,Date()) And Date()-1 which pulls my data for 10/1/2014 to 10/28/2014

I am trying to look a comparable sales data so I need a piece of code that pulls 10/1/2013 to 10/28/2013
0
Comment
Question by:Cole100
4 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40411906
Did you try this:

dateadd("y",-1,DateAdd("d",-DatePart("d",Date())+1,Date()) ) and dateadd("y",-1,Date()-1)
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 250 total points
ID: 40412006
Also try this for month to date last year ...

Between DateSerial(Year(Date())-1, Month(Date()), 1) AND DateSerial(Year(Date())-1, Month(Date()), Day(Date())-1)

ET
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 40412634
You could use for the current year:

    Between DateSerial(Year(Date()), Month(Date()), 1) And DateAdd("d", -1,Date())

and for last year:

     Between DateSerial(Year(Date()) - 1, Month(Date()), 1) And DateAdd("yyyy", -1, DateAdd("d", -1,Date()))

etsherman's expression for the last year is valid too. A matter of taste.

/gustav
0
 

Author Closing Comment

by:Cole100
ID: 40413926
Both etsherman and gustav solution did exactly as i  needed. thank you
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 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

17 Experts available now in Live!

Get 1:1 Help Now