How to pick the n-th row from a query

[Johnny C]

Hi All

I've got a database that looks after problem logs. The users want to be able to pick all queries that relate to 3 fields, Account#, Workstream and update_status. They've asked for a combobox that lists those 3 and the number of logs that relate to each combination. I suggested a few separate comboboxes for each item but no, they want a single box.

So I've created a query that generates those and uses that as the source for a 4 column combobox.

I figured to set BoundColumn to 0 to give me the (n-1)th item in the list (i.e. first item - 0). BoundColumn on any of the columns doesn't give me the combination.

How can I now pull the (n-1)th row from the query to identify the 3 fields in the Combobox selection, so I can select the rows from the database to populate a subform that satisfy the 3 fields in the Combobox selection?

If there's a better way of doing it, I'm all ears, my specialism is Excel and I'm new to this Access lark.



Cheers
Johnny

[Rawb]

It would probably be easiest to do (from an understanding aspect at least), but you could also. . .

Run your normal query. There will need to be some form of sorting/ordering built in (for the next part). Be sure to limit your Query to (n + 1) Records using TOP (n + 1).
Then you Query the results of your first Query using the opposite sorting/ordering using TOP 1.

Example:

You have a Form called frmMyForm with a Text Box called NthRecord.

qryQuery1

SELECT TOP ([Forms]![frmMyForm]![NthRecord] + 1) * FROM MyTable WHERE [Field1]='foo' AND [Field2]='bar' ORDER BY [DateField] ASC

qryQuery2

SELECT TOP 1 * FROM qryQuery1 ORDER BY [DateField] DESC

[Johnny C]

Thanks, but I found a simpler solution, just set some labels to the columns(n) values and change the subform query to use them as criteria