selecting previous from nonsequential

[flect]

I've just created a new report, which is autonumbered, but I want to get the data from the previous report, which may not be serially sequential.

SELECT tblReports.AgencyID, tblReports.ReportID
FROM tblReports
WHERE (((tblReports.AgencyID)=[forms]![frmnavigation]![masterfilter]));

Let's say the returned result is

SINGAPORE 4
SINGAPORE 5
SINGAPORE 7
SINGAPORE 9
SINGAPORE 27

I'm currently working on report #27, how do I let my query know that it should retrieve the data from report #9, - if i was working on report #7, how will it know to retrieve #5?

i initially had a WHERE reportID = [reportID]-1

but obviously that would only get me to report #26,

any ideas?

 

[DCrake]

Simple Software Solutions

Sort the query Descending and select top 2.

Then use DLast(Field,Query)

Singapore27 - Top1
Singapore9 - Top2
Singapore5 - not shown
etc.
etc.



CodeMaster:

 

[khawar]

Download the attached sample

 

[flect]

Excellent! Thanks - that's exactly what I was after.