using a field value in top query 1

[markbrum]

Hi,

Can anyone tell me is it possible to use a number from a field value in a TOP query i.e.

instead of
SELECT TOP 50 FROM...
this
SELECT TOP ([FIELD1]) FROM...

I can't get it to work but maybe my syntax is wrong.

Thanks,
Mark.

 

[rjoubert]

So you're writing your SQL in code (behind a button on a form)?

Try this...

strSQL = "SELECT TOP " & Me.Field1.Text & " FROM...

 

[markbrum]

It's not behind a form. Field1 is one of the columns in the recordset i.e.

SELECT TOP 50 Table1.Field1, Table1. Field2, Table1.Field3
FROM Table1;

to

SELECT TOP (Table1.Field1) Table1.Field1, Table1. Field2, Table1.Field3
FROM Table1;

supposing every record has the same value in Field1.

Maybe I'm doing this the wrong way. What I want to do is select 5 percent of total records but I want to apply some criteria to the selection:-

i.e. if my table's total record count was 100 and I used a select query which used criteria to narrow down the selection to 80 records and I said top 5% then I would get 4 records (5% of 80) instead of what I want which is 5 records (5% of 100) does this make sense?

 

[markbrum]

I changed my approach based on what you said and have now got it working.

Thanks very much for your help.