Placeing Start of month data and End of month date in Report

[Robert M]

I am working on a report that will be generated on a monthly bases, and as such will need to have the beginning of the month and the end of the month dates entered on the report.

How would I be able to do this, if it is possible.

Thank you for your time and your help

Robert M

 

[AUGuy]

Try to be a bit more clear in your question... Are you using the word 'Report' in a specific Access-related sense or as just a set of data you'll present to someone (using an export to excel, etc). Specifically what date context and format are you looking for (time & date report is run, first/last business day, etc).

I've used some progressive date stamps with very good results in my databases so i might be able to help, but a little information in this question would go a long way

Guy

 

[Robert M]

Thanks Guy. The 'Report' is just that, a report that is generated by the report object. I am trying to have two text boxes that will show the first of the month and the last of the month in the report.
Hoping to be able to get the entire database as a point and click function with little to no text entry.

Thanks for asking and hope this is better explination.

Robert M

 

[AUGuy]

Hi Robert,
I'm working on this right now, i'll have something for you soon. Sorry i couldnt reply last night, our internet was down at the house.

Guy

 

[AUGuy]

Robert,
Here is one solution I came up with, this is my FIRST time helping someone in this community so pardon any response faux pas ...

Create a table (in this example named 'ReportDate') with fields 'CurrentMonth' and 'PreviousMonth'.

You can create a Subreport within your Report and add 2 text boxes, one bound to CurrentMonth and one to PreviousMonth.

Then add the following VBA Code:

Function DailyMonthTracking()
'This will test daily the CurrentMonth against the PreviousMonth.
'Upon the start of a new month the table will be adjusted to reflect the
'correct CurrentMonth and PreviousMonth.
 
Dim sCurrentMonth As String
Dim sPreviousMonth As String
Dim rCurrentMonth As Object
Dim rPreviousMonth As Object
Dim dbDatabase As Object
Set dbDatabase = CurrentDb
Set rCurrentMonth = dbDatabase.OpenRecordset("ReportDate")
Set rPreviousMonth = dbDatabase.OpenRecordset("ReportDate")
sCurrentMonth = Format(Now(), "mmm")
 
'Check to see if CurrentMonth has changed each day, if no change exit
If rCurrentMonth("CurrentMonth") = sCurrentMonth Then
    GoTo EndDailyMonthTracking
Else
'If change, adjust table and exit
    rPreviousMonth.Edit
    rPreviousMonth("PreviousMonth").Value = rCurrentMonth("CurrentMonth")
    rPreviousMonth.Update
    rCurrentMonth.Edit
    rCurrentMonth("CurrentMonth").Value = sCurrentMonth
    rCurrentMonth.Update
    GoTo EndDailyMonthTracking
End If
EndDailyMonthTracking:
End Function

For the first month this is run itll only fill in the Current Month field, you'll have to manually put in the value for previous month just this once. After that, all subsequent months will properly change the table to its correct values.

Just have your database call this function on open and voila, you have a working month tracker that requires no user input.

Hope this helps,
Guy