How to view a monthly reports on the 1st of every month (1 Viewer)

[donkey9972]

I have my reports setup so I can view all the data for a single month in a report all the way up until the last day of the month. However , I still need to be able to view that data on the 1st of every month, but only the data from the previous month. I have no idea how to make that happen. Any ideas at all?

 

[CJ_London]

yes - select a date range which is the previous month.

Plenty of ways to do that using dateadd function to subtract the day and the month from today’s date.

Or use a table to store the ‘current range’

Or on your form have a couple of text boxes to specify the range

 

[donkey9972]

Ok, so if I was to just click a button, to print preview the report on the 1st of the month, which way would be best to accomplish this?

 

[MajP]

To make this very resuseable (assuming you may want to look at old month reports)
1. Make a simple form
2. Put a combobox that returns a list of years (2020, 2021)...
3. Put a combobox that returns a month value (1...12)
4. Then you can put this code in a button event something like this

Public Sub OpenReport()
  Dim themonth As Integer
  Dim theYear As Integer
  Dim startDate As Date
  Dim endDate As Date
  Dim StrStart As String
  Dim strEnd As String
  Dim strSql As String
 
  If IsNumeric(cmboMonth) And IsNumeric(cmboYear) Then
    themonth = cmboMonth
    theYear = cmboYear
    startDate = DateSerial(theYear, themonth, 1)
    endDate = DateSerial(theYear, themonth + 1, 0)
    StrStart = "#" & Format(startDate, "MM/dd/yyyy") & "#"
    strEnd = "#" & Format(endDate, "MM/dd/yyyy") & "#"
    strSql = "Created_date between " & StrStart & " AND " & strEnd
    'Debug.Print strSql
   DoCmd.OpenReport "rptData", acViewPreview, , strSql
  End If
End Sub

If the user selects Jan, 2024 from the combo it shows this month records.
If they select any other combo it will show that month and year.
Change the control, report, and datefield names to your names.

This is one of the tricks for finding the last day of any month
DateSerial(theYear, themonth + 1, 0)

If you want to do this without a form and only return this months. Then the Criteria of the report query would look something like.

SomeDate between DateSerial(year(date),Month(date),1) AND dateSerial(Year(date),Month(Date) + 1, 0)

 

[CJ_London]

depends on which way you want to do it, how your report works etc. You could modify the underlying query to your report or provide a filter to your report (assuming you are using docmd.openreport).

In one of my apps, I preview the report in a subreport on the form and have a listbox which lists months in the format yyyy-mm. I have the same format of the transaction date as a field in the report. I have set the subreport linkchild/master to field and the listbox. changing the selection in the listbox updates the subreport

edit: I see MajP has posted something similar to what I do

My code for printing a report is

Function ExportReport(Rep as string, Crit as string, rPath as string) as Boolean

    On Error GoTo errHandler

    DoCmd.OpenReport rep, acViewPreview, , crit, acHidden
    DoCmd.OutputTo acOutputReport, rep, acFormatPDF, rPath
    DoCmd.Close acReport, rep, acSaveNo
    ExportReport=True

Exit Function
    
errHandler:
    glErrHandler Err, Err.Description, "ExportReport"

End Function

rep = name of report
crit = the criteria to filter the report
rPath = the full path and file name for the pdf to be created

 

[Pat Hartman]

To expand on @MajP 's solution. I generally populate the from/to controls with the default values when the form opens. So the first workday of a month (not just the 1st), I would use last month as the range. Any other day, I use this month. Then the user can choose to change one or both to change or expand the range.

If you use unbound date controls rather than the combos, be sure to specify a date data type so that Access only allows valid dates to be typed.