Date Formula for "four oldest dates"

[edteck]

How do I compile a formula which will pull out the "four oldest dates" from a date field? I am trying to finalise a crystal 7 report built on database history and which gives the status of jobs which have not yet been done, for whatever reason. The dates on which the jobs went on request are listed, but I simply need the four oldest dates.
Help!

 

[AAP]

What I usually do in this situation is to run a query on my datasource, output the subset to a temporary database, then point my report at the temporary database.

 

[retro]

if you grouped your report by date and inserted a summary on date (and move into into the group header) you can then use the Top N\Bottom N feature. In here you can choose to show the top N or bottom N instances within a given field. So you could set it to show records only where the date is in the bottom 4, ie the 4 oldest ..

R

 

[edteck]

I cannot create a temporary database (not permitted), therefore I need a way round it.

 

[edteck]

Thanks Retro. This does the trick.

 

[swilliams]

If you base your report on a query (Access) or view (SQL Server) then you can select the four oldest records in the query / view and you don't have to bother about trying to configure the report to do this.

If you are wandering, the SQL statement would be 'SELECT TOP 4 fields FROM tables ORDER BY datefield,...

The advantge of doing this (this also applies to calculations,...) is that the processing is being done in the native database and so is quicker to execute.