Listbox last 6

[David B]

Hey guys'
I want to have a list box on a report that shows the last 6 records only. I have another report that I hope to show all records in the DB (or that meet a criteria), but its this snapshot on the top of the report that the manager just wants the last 6 events/records.

Hope someone has a way to show this.
thanks in advance David B

 

[xenou]

I think you can drop the first report into the seconds report header as a subreport. You can probably do this with a drag and drop in design view.

ξ

 

[David B]

Thank for that input. I didnt think about that step. But I am now.
But my initial req for help stands. My table has a few Yrs of data we cant trim that at all. (Data retention)
But the manager wants a report that shows the last 6 rows in the datatable. ie last 6 months for selected fields. I tried to sort Descending but sure there is a way I can tell the qry to only show me last 6 records.
Hope someone has an idea!

 

[xenou]

If last 6 months and last 6 records are the same thing, use a Max() criterion:

Select field, field, field WHERE field >= (SELECT Max(date_field) - 200))

Though you'd probably want to use a datepart or datediff kind of formula for more precision. Or even a DateSerial with Month - 6. Offhand I'm foggy on the details (obviously). But it's your typical datehandling functions that you rely on, whichever they are.

 

[revans]

If you can sort descending you can use the Top predicate like this

SELECT TOP 6 MyDateField, OtherFields
FROM MyTable
ORDER BY MyDateField DESC;

Or something like that.

hth,

Rich

 

[swGeof]

Have you tried the Return drop down box in the query. If you sort the query the right way you can type in 6 in the Return and it shoudl work.

 

[David B]

Hey guys.
Ravans hit it on the spot. So simple. Im embarrassed I didn't think or know that.
Thank you very much. I really appreciate it.
david