Retrieve First and Last Record

[hootie318]

I have a report that compiles all complaints. I want to grab the date from the first record and the last record from the report and put it in the report header, then Long Format the date to show only the month and year. Can anyone help on this one?

 

[Kelemit]

2 options that I can think of... sorta.

or 3, 2 are variations of each other.

Create 2 queries, returning min and max of the dates for the complaints you're getting. Make 2 text boxes with control source = to those queries, formating the box to be MM,YYYY.

If teh query is too complicated to run in the text box, you can just make a form with 2 subforms with the queries as their record source returning the values you need. On the reports On-Load, open the form hidden, on the reports On-Close, close the form. Make 2 textboxes on the report, have them =Forms!HiddenFormName!TextBoxWithDates

These basically are the same thing.

Next option: VBA. If you can.

 

[QTNM]

Hope this helps ....

Here you go ...

Create a label in the header of your report, in this case 'lblDateRange'. I just made a psuedo table 'tblDates', then use the vba below in your open event of your report.

Hope that helps ....


Public Sub Report_Open(Cancel As Integer)

Dim db As Database
Dim rs As Recordset
Dim sql, range As String

Set db = CurrentDb()

sql = "SELECT Min(tblDates.Dates) AS MinOfDates, Max(tblDates.Dates) AS MaxOfDates FROM tblDates;"
Set rs = db.OpenRecordset(sql)

range = Format(rs.Fields("MinofDates"), "mmm yyyy") & " to " & Format(rs.Fields("MaxofDates"), "mmm yyyy")

Me!lblDateRange.Caption = range

rs.Close
db = Nothing

End Sub