Select row from table dependant upon value within textbox

[IANGRAND]

I want to select a row number which is specified from a textbox. This number changes depending on the qantity of data been assessed. I have tried the following SQL but to no avail.


SELECT TOP 1 *
FROM [Select top [Text156] * from HOURLY_AVERAGE order by AvgOfNO2 desc]. AS T1 ORDER BY AvgOfNO2;

Any ideas

Cheers

 

[SkipVought]

Hi IANGRAND,

Where is your WHERE clause and TextBox Value???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BNPMike]

I personally don't understand your SQL. In case no-one else can, can you translate it into English?

 

[lespaul]

Iangrand has been working on this for a while. If you haven't seen the previous threads, this query doesn't make sense.

If I understand (and remember from the previous threads) correctly Text156 is on a form and the user enters the number of rows to return. However, the query then states select TOP 1, so I'm not quite sure what the user input is suppose to do.

Leslie

 

[IANGRAND]

Ive worked out the solution to be:
Text163 calculates the row number to be applied to sql statement as follows:
=DLookUp("[CountOfExpr1]","COUNT_HOURLY_AVERAGE_DAYS")*0.0022

Then the row number specific to the above statement is found, and dumped into textbox170.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT TOP 1 * FROM [Select top " & Text163 & " * from HOURLY_AVERAGE order by AvgOfNO2 desc]. AS T1 ORDER BY AvgOfNO2;")

If Not rs.EOF Then
VALUE = rs(1)
Else
MsgBox "There were not records for that RBU", vbOKOnly
End If

Text170 = VALUE

Perhaps now you can see what i was trying to calculate

 

[lespaul]

Thanks for the explanation, glad you got it working.

leslie