Solved

MS Access Query - current month

Posted on 2014-04-01
4
2,810 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
  • 2
4 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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 10

Expert Comment

by:HuaMinChen
Comment Utility
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 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

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

11 Experts available now in Live!

Get 1:1 Help Now