Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-07
3
Medium Priority
?
633 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

877 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