Filter on last Records Displayed

M

Michael Kintner

How can I tell in a report to show me only the last 5 records in a list.
Without doing a field record match. I just want it to display only the last
5 records of the total 100 in the list.

Thank you in advance for your help!
Mike
 
T

tina

use a query for the report's RecordSource (rather than a table). sort the
records in the query in Descending order, so the last record is first. then
add the TOP predicate to the SQL statement, as

SELECT TOP 5

or, in query design view, go to the Top Values droplist on the toolbar, and
enter a 5.

hth
 
D

Douglas J. Steele

Just be aware that it's possible to get more than 5 records this way. If
there's a tie based on the sort field(s), more than 5 rows may be selected.
 
M

Michael Kintner

I found this problem, also sometimes I don't get any records when records
exist. So how can only setup in a report to only show the last 4 records
from the query?
 
T

tina

well, if the query isn't returning any records when you know that there are
records that meet the criteria, then there's either a problem with the query
or with the table(s). can you post the query's SQL statement?

as for the report, you can control the number of records that display in the
detail section of the report (i haven't tried this solution for
header/footer sections).
open your report in design view and add an unbound textbox control named
txtCount to the Detail section of the report.
set the control's Visible property to No, the ControlSource property to =1,
and the RunningSum property to Over All.
add the following code to the report Detail section's Format event
procedure, as

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me!txtCount > 5 Then
Me.Detail.Visible = False
End If

End Sub

you can use this solution with either a SELECT TOP query, or an ordinary
SELECT query.

hth
 
Top