Solved

MS Access Query - current month

Posted on 2014-04-01
4
3,000 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 50

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 10

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 50

Accepted Solution

by:
Gustav Brock earned 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

733 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