Solved

Report to display Current Month Data and Previous X Month's data.

Posted on 2006-07-24
3
259 Views
Last Modified: 2011-10-03
I am building some reports of data that is entered each month.  I need to generate reports that contain the data for this month as well as the previous X months.  I am having trouble with building the subtraction of these dates.  I have a form with 3 dropdown menus.  One for month (Jan, Feb, ...) one for Year (00, 01, 02,...), and one for Number of months (1, 2, ...).

How can I build a query off this so that when I select Feb 06 and previous 4 months a query gives me reports for:
Feb06
Jan06
Dec05
Nov05  

Is there a way to subtract dates?

Thanks!
0
Comment
Question by:busbeead
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17167921
You can make use of DateAdd

e.g
dateadd("m",-4,currdate)

"m" = months
-4 = back 4
currdate = based from this date

Now in your query, for your selected date that u want t check against, u can do a between
e.g.

where somedatefield between mydate and dateadd("m",-4,mydate)
0
 

Author Comment

by:busbeead
ID: 17168617
That is exactly what I was looking for.  I had been trying to build all these stupid tables to figure this out.  Thanks so much for such a quick solution!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17170449
No probs!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

770 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