Member Avatar for dreamer_lek91

I have these few dates in my database and I would like to have the latest date to be shown at my report footer.

03 dec 2001
15 may 2010
08 mar 2007
14 jan 2004
29 sep 2009

can any kind soul plz help me?=) thx alot in advance=)

  • 2 Contributors
  • 1 Reply
  • 386 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by boblarson

Dani AI

Generated

supplied the sample dates and the goal is clear: show the single most recent date in the report footer. 's quick domain-aggregate approach is a good quick fix, but a few alternatives and caveats make the result more robust in real reports.

A simple, fast option is an aggregate query that returns the MAX date and then expose that field to the report. Example SQL (replace names as needed):

SELECT MAX([MyDateField]) AS LatestDate
FROM [MyTable];

That query can be saved and used directly, or embedded in the report's recordsource as a subquery if the report also lists full records:

SELECT t.*, (SELECT MAX([MyDateField]) FROM [MyTable]) AS LatestDate
FROM [MyTable] AS t;

If the report must remain on the full table and an unbound footer textbox is preferred, populate it from VBA (DAO) on open/load — this avoids domain-aggregate calls and gives control over nulls and formatting:

Private Sub Report_Open(Cancel As Integer)
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT MAX([MyDateField]) AS LatestDate FROM [MyTable];")
    If Not rs.EOF And Not IsNull(rs!LatestDate) Then
        Me.txtLatestDate = Format(rs!LatestDate, "dd mmm yyyy")
    Else
        Me.txtLatestDate = "No dates found"
    End If
    rs.Close
    Set rs = Nothing
End Sub

Key pitfalls and tips: ensure the column is actually Date/Time (if stored as text, SQL/MAX returns a lexical maximum — use DateValue conversion or normalize to Date/Time). For per-group "latest" values put the aggregate in a group footer; for the whole-report max use an aggregate control like =Max([MyDateField]) in the report footer. For large tables, prefer SQL/recordset approaches over repeated domain aggregates for better performance.

Member Avatar for boblarson

Control Source of the text box:

=DMax("YourDateFieldNameHere", "YourTableNameHere")

Replace YourDateFieldNameHere with your actual field which has the dates and YourTableNameHere with the name of the table where the date field resides. And KEEP THE DOUBLE QUOTES.

Edited by boblarson because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.