?
Solved

Access 2010 SQL Query - Looking up last month's data

Posted on 2013-01-07
3
Medium Priority
?
634 Views
Last Modified: 2013-01-07
Greetings Experts!

I am trying to create a query that looks up dates only from last month. You'll laugh, but here's the where clause I had and it worked:

WHERE (((DatePart("m",[Work week],0))=DatePart("m",Date(),0)-1));

UNTIL January :). I also realized that i wasn't checking the year...

I looked around online, and one answer was to use

DATEDIFF(m,[Work Week],date())=1

But that threw an invalid procedure call error.

All of the other stuff I saw was just wrong like my first attempt :)

As always, your help is greatly appreciated.

Thanks!
0
Comment
Question by:billg7
3 Comments
 
LVL 26

Accepted Solution

by:
jerryb30 earned 2000 total points
ID: 38753116
WHERE DateSerial(Year([work week]),Month([work week]),1)=DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1))
0
 
LVL 1

Author Closing Comment

by:billg7
ID: 38753160
Beautifully simple. Love it! Thank you!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38753167
use a where clause like this

where [work Week] between dateserial(Year(now()),Month(now())-1,1) and dateserial(Year(now()),Month(now()),0)


dateserial(Year(now()),Month(now())-1,1) will give you  12/1/2012

dateserial(Year(now()),Month(now()),0) will give you  12/31/2012
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month5 days, 14 hours left to enroll

589 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