MS Access Query - current month

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
dkilbyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
COACHMAN99Commented:
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
 
Gustav BrockCIOCommented:
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
 
HuaMinChenBusiness AnalystCommented:
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
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.

All Courses

From novice to tech pro — start learning today.