?
Solved

MS Access Query - current month

Posted on 2014-04-01
4
Medium Priority
?
3,137 Views
Last Modified: 2014-04-02
I am trying to create a couple of queries in MS Access that when opened show me all the data for the current month, and then another query that shows me all the data for the previous month, without having to enter dates, is this possible and if so how.

The date field is [Date of Call]

Thanks
0
Comment
Question by:dkilby
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39971004
SELECT Tx.TX_ID, Tx.Cust_ID, Format([TX_Date],'mmm/yyyy') AS MM
FROM Tx
WHERE (((Format([TX_Date],'mmm/yyyy'))=Format(Now(),'mmm/yyyy')));

SELECT Tx.TX_ID, Tx.Cust_ID, Format(DateAdd('m',-1,[TX_Date]),'mmm/yyyy') as MM
FROM Tx
WHERE (((Format(DateAdd('m',-1,[TX_Date]),'mmm/yyyy'))=Format(DateAdd('m',-1,Now()),'mmm/yyyy')));
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39971775
You should never use mmm in SQL as it will fail in a non-English environment.

Use DateSerial.

This month:

Where YourDate Between
    DateSerial(Year(Date()), Month(Date(), 1) And
    DateSerial(Year(Date()), Month(Date() + 1, 0)

Previous month:

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

/gustav
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39971782
Try
... Format ([Date of Call], "yyyy/mm")=Format (Date(), "yyyy/mm") or Format ([Date of Call], "yyyy/mm")=Format (DateAdd("m",-1,Date()), "yyyy/mm")

Open in new window

0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39971802
Why all this unneeded formatting? It will prohibit the use of an index on [Date of Call].

Above, replace YourDate with [Date of Call], and a closing parenthesis was missing:

Where [Date of Call] Between
    DateSerial(Year(Date()), Month(Date()), 1) And
    DateSerial(Year(Date()), Month(Date()) + 1, 0)

Previous month:

Where [Date of Call] Between
    DateSerial(Year(Date()), Month(Date()) - 1, 1) And
    DateSerial(Year(Date()), Month(Date()), 0)

/gustav
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.
Suggested Courses

801 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