DLookup Query and return next record based on value in textbox

[ccondran08]

I have a form which has a textbox (named txtTopTrend1 =[qry_Top_Trends_Top1 subform].[Form]![ID #]) that is linked to a subform that is has one record in the query (Top Values = 1). I also have a similar query that displays the top 5 values in a query called "qry_Top_Trends_Top5". There are only 2 fields in the query called "ID" and "ID #" so I would expect this to be a simple exercise.



I have linked the textbox to the subform because I want to use the textbox value to retrieve the next value in the query so I can show the 2nd highest ID# out of the top 5 in the query and work my way backwards until I have 5 text boxes with one text box for each of the top 5 values in the query.

I have tried the following DLookup to retrieve the 2nd record in the query based on the top value in the textbox (txtTopTrendID) however the value is returning blank. Can anyone spot the obvious here or is another way I can retrieve the 2nd (3rd, 4th & 5th) record value from a query ? :banghead:

=DLookUp("[ID #]","qry_Top_Trends_Top5","ID= " & [Forms]![frm_Reports]![txtTopTrendID]+1)

 

[arnelgp]

2nd:
=DLookUp("[ID #]","qry_Top_Trends_Top5","ID> " & [Forms]![frm_Reports]![txtTopTrendID])

 

[ccondran08]

Brilliant, thanks arnelgp, it works with ;

ID< " & [Forms]![frm_Reports]![txtTopTrendID])

instead of ID> " & [Forms]![frm_Reports]![txtTopTrendID])