SQL query

[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

 

[SkipVought]

Hi
[t]
select top 1 * from (Select top Me!Text156.Text * from mytable order by myfield desc) as T1 order by myfield asc

[tt]


Skip,

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

 

[IANGRAND]

Ive inserted the code you suggested, the query looks like:

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

However, on running the code the following error is produced:

The select statement includes a reserved word or statement that is mispelled or missing, or the punctuation is incorrect.

Any ideas?

Cheers

Ian

 

[solas989]

If you are running this in VBA (which likely you are if it is mousemove) then you need to set apart the text field like a variable (at least this is what I do):

select top 1 * from (Select top " & Me.[Text156] & " * from mytable order by myfield desc) as T1 order by myfield asc

HTH

 

[IANGRAND]

Solas989 the code you suggested doesn't work....any ideas