Solved

Query criteria to get previous month's data not working

Posted on 2016-11-01
3
25 Views
Last Modified: 2016-11-01
I have a date field in a table and would like to enter query criteria to display the previous month's data, but every date function I've tried yields no results, even though there definitely are records from the previous month.  I suspect there's something wrong with the way the date is stored but don't know how to pin it down and resolve it.

The date is stored as a short date and is formatted to display as mm/dd/yy.    Query criteria along the lines of:  =DateAdd("m", -1, Date()), or  =Month(Date()-1), or =Month((Now)-1)), and various permutations of DateSerial yields no records.  I tried making a query with just the date field so I could try different date functions on it, but no luck.

Thanks in advance for any suggestions.
0
Comment
Question by:Cindy Aitken
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
You would use as criteria:

    Between DateSerial(Year(Date()), Month(Date())-1,1) And DateSerial(Year(Date()), Month(Date()),0)

/gustav
0
 

Author Closing Comment

by:Cindy Aitken
Comment Utility
Thanks, Gustav!  That works!  Wouldn't you know, five minutes after I posted my question, I found another solution:  Year([AsnmDate])*12+DatePart("m",[AsnmDate])=Year(Date())*12+DatePart("m",Date())-1.  This statement and yours both solve the problem.  I wonder why the simpler DateAdd or Month statements wouldn't work, but I'm glad to have a solution.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

And believe me ... there are so many clumsy "solutions" out there.

/gustav
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

772 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

15 Experts available now in Live!

Get 1:1 Help Now