SQL Query - Selecting specific rows from a table

[IANGRAND]

I have a Textbox (Text156) which calculates the row number that I need to find using the following code:
=DLookUp("[CountOfExpr1]","COUNT_HOURLY_AVERAGE_DAYS")*0.0022

I then have a SQL query, which I am attaching to mousemove on Textbox157. This query is:

select top 1 * from (Select top 19 * from mytable order by myfield desc) as T1 order by myfield asc

But I want to replace 19 with the value currently found in Text156.


Any suggestions

Cheers

Ian Grand

 

[batteam]

I'd like to help. Can you provide a little more explanation of what overall your trying to do. I'll check your reply tomorrow - 04/16/2004.

Till then.

 

[dhookom]

The Mouse Move event is a semi continuous event that fires as you move your mouse over a control or form. I doubt this is what you want. You should also do yourself a favor and give proper, meaningful names to Text156 and CountOfExpr1. Then, explain how you are using the query. Is this the record source for a form or row source of a list or combo box or what?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[IANGRAND]

Ive designed an Access database alongside an external converter written through vb6. The database continually downloads air quality data taken from a monitor within the borough of Rushcliffe. The data is run through the converter, then automatically written to a new table within the database. This data is then analyed and the results for the period graphed, alongside the max, min, average, time period and datacapture values.

The final step is to calculate the 98th percentile, which is to be calculated through the following code: =DLookUp("[CountOfExpr1]","COUNT_HOURLY_AVERAGE_DAYS")*0.0022
This code calculates the row number that should represent the 98th percentile, but obviously depending upon the number of values analysed the row number will alter. This row number is placed in Texbox156.


Linking the code to mousemove does work, so long as it is only likned to the textbox, rather than the entirety of the form. So all i need to find out is why the piece of code below does not work:

select top 1 * from (Select top 19 * from mytable order by myfield desc) as T1 order by myfield asc

But I want to replace 19 with the value currently found in Text156.


Unfortuantly I do not want to start changing textbox numbers, as these are linked into other parts of the system and this will require reprogramming certain aspects of the system


Cheers

Ian Grand

 

[dhookom]

Instead of selecting the top N number of records, would it work to select the top 98% or similar?

You can change the SQL of a query using DAO.
CurrentDb.QueryDefs("qselYourQuery").SQL = "SELECT...."

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[IANGRAND]

Doesn't work like that pal, must use the method that i mentioned previously

 

[dhookom]

Your syntax of:
select top 1 * from (Select top 19 * from mytable order by myfield desc) as T1 order by myfield asc
Worked for me in a test query. I did suggest a method where by you can use code to modify the SQL of a saved query using DAO.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[IANGRAND]

I know that the above code works, the problem is that I don't need to find the 19th row, I need to find the row indicated by the value stored in the textbox Text156.

 

[dhookom]

I understand the "nth row" will change.
I assume you have a query with the sql you described. You need to be able to change/update the sql of that query to include your changing number. I have stated twice how to use code to change the sql of a saved query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.