Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

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

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
billg7
Asked:
billg7
1 Solution
 
jerryb30Commented:
WHERE DateSerial(Year([work week]),Month([work week]),1)=DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1))
0
 
billg7Author Commented:
Beautifully simple. Love it! Thank you!
0
 
Rey Obrero (Capricorn1)Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now