X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Report Data From Last Month, Access 2016    
post Oct 2 2019, 02:15 PM

Posts: 130
Joined: 16-March 15
From: Chautauqua, NY

Hi again,

I use this code with a button on an unbound form to get the data for a report. It takes the current month and year and puts them into two texts boxes and then goes forward as written. It works great.

The unbound form has two text boxes I can manually enter the month and year I want and then a button that processes that info. Or I can press this magic "this month button" and get "This Month".

Private Sub btnThisMonth_Click()
    txtMonth = Month(Date) 'txt box on form
    txtYear = Year(Date) 'txt box on form
    Me.Visible = False ' make form disappear when opening the report
    DoCmd.OpenReport "rptCallMonthly", acViewPreview 'open the report
End Sub

Report opens using tis SQL:

SELECT tblCall.callID, tblCall.callNumber, tblCall.callDate, tblCall.callType, tblCall.callFalseAlarm, tblCall.callCancel, Month([callDate]) AS callMonth, Year([callDate]) AS callYear
FROM tblCall
WHERE (((Month([callDate]))=[Forms]![frmCallMonthlyReportChooseDate]![txtMonth]) AND ((Year([callDate]))=[Forms]![frmCallMonthlyReportChooseDate]![txtYear]));

But now I have a request to have button that would put last month's data in the report. So on October I run a report for September data.

I have looked a lot of examples of ways to get last month's data. But I cannot figure out how to get any of them into VBA.

Clear as mud? Thanks for the help.

Best, Scott
Go to the top of the page
post Oct 2 2019, 02:21 PM

Posts: 1,510
Joined: 2-April 09
From: somewhere out there...

Private Sub btnLastMonth_Click()
    txtYear = Year(DateSerial(Year(Date), Month(Date), 0)) 'txt box on form
    txtMonth = Month(DateSerial(Year(Date), Month(Date), 0))  
    Me.Visible = False ' make form disappear when opening the report
    DoCmd.OpenReport "yourReport", acViewPreview 'open the report
End Sub

This post has been edited by arnelgp: Oct 2 2019, 02:22 PM

Never stop learning, because life never stops teaching.
Go to the top of the page
Jeff B.
post Oct 2 2019, 02:24 PM

UtterAccess VIP
Posts: 10,326
Joined: 30-April 10
From: Pacific NorthWet

… hmmm, what will you do when the users ask for a button for "this month, last year", then another for "3 months ago", and another for …

Another approach might be to have your text boxes default to the Month(Date()) and Year(Date()), but allow the users to change the values if they decide they want, say "6 months ago". This approach would require zero buttons.


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post Oct 2 2019, 04:35 PM

UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA

I generally dislike free text boxes for tasks of this nature. I prefer either combo or list boxes that offer a set of valid options so users can select one by clicking it, rather than having to type in a value allowing the chance they may fumble a bit in the process.

I suspect you have a valid range of dates available based on the table from which you are querying records for the report. Yes?

You need two Combo or list boxes like this:

SELECT DISTINCT Year(tblYourSourceTableNameGoesHere.ReportDate)
FROM tblYourSourceTableNameGoesHere ORDER BY Year(tblYourSourceTableNameGoesHere.ReportDate)


SELECT DISTINCT Month(tblYourSourceTableNameGoesHere.ReportDate)
FROM tblYourSourceTableNameGoesHere ORDER BY Month(tblYourSourceTableNameGoesHere.ReportDate)

That means only dates that are actually in your database can be chosen, and any month and year can be chosen simply by clicking on values in the two controls.

Also, I wonder if you are going to get a request at some point for a report that covers two months, or a quarter, or a full year....

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Oct 2 2019, 06:20 PM

UtterAccess VIP
Posts: 3,632
Joined: 19-August 03
From: Auckland, Little Australia

I generally always prefer the end users to basically have control over their own reports. For this, I make use of Excel and pivot tables.

Depending on your data, and how 'live' it needs to be, I would populate a Excel workbook, and have prebuilt pivots in it. Workbook opens to main page which I usually have some metrics, and hyperlinks to different reports, that are generally all different views of the same data. This month, previous month, this time last year, last 6 months, 12 months etc.

I have report templates on a network, and build a report on a network drive, with a datestamp in the name. I then have another file that users have a shortcut to, and this will go through the folder and get the latest file, and copies it down to the users local drive, with a more user friendly name. This can be setup easily for multiple report types e.g. Sales data might be a different schedule than Call data etc.

I have lots of code and use this a lot, if you need more info, let me know. Code for things like when the report is being built, users get a message saying it cant be opened yet, and to try again later. Each report has a datestamp on it, so each user can be sure they are reading the same reports as other users, and lots more. I find Access reports very limited, in fact the last time I used one was with barcodes to be printed and scanned.

Also, building reports in Excel has other advantages, users dont need Access installed - generally not supported in most places I work in.

Beer, natures brain defragging tool.
Go to the top of the page
post Oct 2 2019, 06:54 PM

UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV

As long as we're voicing what we "generally like" let me say that I like to avoid using Date() because it can lock the user in. It's better to have a textbox on the format with Date() as the default value. Then reference the textbox in your code instead of Date(). Set the defaults value of the textbox to Date(). The user does not have to do any additional work in the usual case.

Also, Excel pivot tables are great. The user does not need to have a pre-defined template for this.

Robert Crouser
Go to the top of the page
post Oct 2 2019, 11:22 PM

Posts: 130
Joined: 16-March 15
From: Chautauqua, NY

Wow. That's a lot of information and a lot to think about.

So, this is a report that is run each month. It is always run in the first week of the month for data from the month before to provide numbers for a meeting. The people who run it are end users that do not have any special knowledge of databases in general or Access specifically. They simply open the application and click three buttons, enter the month in one text box and click another button. I have put the current year in the second text box as a default. The two text boxes need to be able to take other input as there are a few people who can run the report for other month/year combinations. As it is it works fine for the purpose it was built for. I was asked if there could be a button that would eliminate the need to add the month, just click a button for the report that, by default, runs the last month's data report. I said I would look into it.

So that is why I asked. I was hoping that there was a way to code in last month. If it doesn't work or is not worth the effort I will just say it cannot be done and that will be end of it. I appreciate all the advice on ways to go about providing report information. I will re-read this post to try and incorporate these ideas in my work. Thank you.
Go to the top of the page
post Oct 2 2019, 11:53 PM

UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV

As long as there's a way to specify any month if the need arises then I think arneldp's code should be okay for your one-click last month report.

Robert Crouser
Go to the top of the page
post Oct 19 2019, 08:45 PM

Posts: 6
Joined: 16-July 18

To subtract 1 month from todays date use dateadd("m", -1, date()). Then feed that value to the same function you're already using.
This post has been edited by bsm2th: Oct 19 2019, 08:47 PM
Go to the top of the page
post Oct 20 2019, 04:42 AM

Posts: 7,112
Joined: 22-December 10
From: England

In the past, I've used a table for fiscal periods
Start Date, End date, and a couple of field for what to call the period (1901, 1902 etc)
Use names that people will use.

From there for reports like these display a list of periods in combo boxes (maybe from period, and to period)

From there a relatively simple query will pick up the actual from date for the from. and the todate of the to
Run your report picking up the transaction date you want, and can also display the period

You do have to add to the fiscal table once a year, and you do have to make sure that there are no period overlaps, and that you handle any 'dates' that are actually timestamps.

I do that with a saved overall query to which I add the date criteria when I run the report

Select from saved query where Transaction date between userfrom and user to where the from and to are taken from the form.

This will continue to work when the company decides that it wants four and five week periods, or wants to end on say the fifth of the month, and relies only on the fiscal table being correct, and the user knowing which period they want to report on.

Suppose the company decides that for December it wants to end on the 5th Jan. - Just update the fiscal table.

And it's easy enough to set the choices to 'last period' when the form opens. (For a report for a single period, both from and to boxes would be the same)

This post has been edited by dmhzx: Oct 20 2019, 04:47 AM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    18th November 2019 - 04:38 AM