I have a small data warehouse I've built in MS Access 2016. I load new data in the warehouse every month, and I want to dynamically show users of the data warehouse the date range available for reports.
I created a label that says the following: "*Data loaded for January 1, 2016 through the end of".
Next, I created a textbox named txtMaxDate. I gave the textbox a transparent border and a font that matches the label above. I placed the textbox right after the label.
In the onLoad event of the form, I wrote the following code:
Private Sub Form_Load()
Dim sql As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim maxLoadDate As Date
Dim theYear As String
Dim theMonth As String
Set db = CurrentDb
sql = "SELECT MAX(PayYearMonth) as MaxDate FROM tblPaySummary"
Set rs = db.OpenRecordset(sql)
rs.MoveFirst
maxLoadDate = rs!MaxDate
theYear = year(maxLoadDate)
theMonth = MonthName(month(maxLoadDate))
Me.txtMaxDate.Value = theMonth & " " & theYear
End Sub
The code gets the most recent date from the database and displays the date in the text box.