Selecting "N" Number of records

[Toya2176]

I was wondering if it was possible to select any given number of records without changing Top Value in the Queries properties. I want to be prompted : "Please enter # of records to return". I don't need any particular order because I have a mod already set up to randomly pick what ever amount of numbers are picked.

All help is appreciated
Thanks in advance

 

[lameid]

I couldn't get a top value to take a parameter which means you will have to concatenate the literal into a SQL string and use the SQL string appropriately (set the recordsource of a report, set the SQL property of a query or open a Recordset as the SQL statement).

If you need more help, please let us know about what your trying to do so we can help. Also make your query take the top 5 values (or some arbitrary value) and switch to SQL view. Copy and paste that SQL statement into your post someone can give you a good code sample.

 

[Toya2176]

Thanks LameId, but I don't want to set a fixed number. Basically at any given time any given number of records will need to be selected. I don't mind myself setting the top value in the SQL properties, but users won't understand how to do that.

Here is what the SQL view shows :

SELECT DISTINCT TOP 5 [Heading Query].HeadingID, [Heading Query].[File:], [Heading Query].Location, [Heading Query].Building, [Heading Query].[File Clerk], [Heading Query].[Technical Order No], [Heading Query].TmpRandNbr, [Heading Query].QTY
FROM [Heading Query];

I'm really new at this so if this sounds stupid please ignore me..But is it possible to have it say:

Select Distinct TOP N (some variable) and then assign a number to N by some input prompt??

Will that work?? And how could I do that??

 

[lameid]

Allow me to rephrase... No you can't simply get a query to do that. You will instead have to use code to make an SQL statement that puts a variable into the statement. SQL is the language of querys.

The below procedure will create a query with the name strNewquery and return the top LngTopRecords (you can copy and paste it into a module).


Sub TopXRecords (strNewQuery as string, LngTopRecords as long)
dim qry as querydef
dim strSql as string

strSQL = "SELECT DISTINCT TOP " & LngTopRecords & _
" [Heading Query].HeadingID, " & _
"[Heading Query].[File:], " & _
"[Heading Query].Location, [Heading Query].Building, " & _
"[Heading Query].[File Clerk], " & _
"[Heading Query].[Technical Order No], " & _
"[Heading Query].TmpRandNbr, [Heading Query].QTY " & _
"FROM [Heading Query];"

set qry = currentdb.createquerydef (strNewQuery, strSQL)
set qry = nothing

End Sub