Displaying random fields. How do I determine how many?

[liamm]

Hi,
I have the following code for a query

SELECT TOP 25 ID, Question, Answer
FROM RefresherQuestions
WHERE (((RefresherQuestions.[Plant Section ID])=2))
ORDER BY RND(ID);

What I would like is to have the facility for the end user to be prompted on How Many fields should be generated. At the moment the code displays 25 randomly selected fields.

If you can help, GREAT
If I can help, EVEN BETTER

 

[raskew]

Believe you'll have to create this using code. Try this:

Function RanVaries(PQryName As String, pNum As Integer) As String
'*******************************************
'Name:      RanVaries (Function)
'Purpose:   Allow user to modify a Top statement
'           in a SELECT Top query
'Inputs:    From debug window:  ? ranvaries("xyz", 25)
'Output:    Query xyz created and saved
'Arguments: PQryName: Name of query to be created.
'           pNum: Number of records to be returned.
'*******************************************

Dim db As DATABASE
Dim qd As QueryDef
Dim strSQL As String, tName As String, test As String

Set db = CurrentDb
strSQL = "SELECT TOP " & pNum & " ID, Question, Answer" _
       & " FROM RefresherQuestions" _
       & " WHERE (((RefresherQuestions.[Plant Section ID])=2))" _
       & " ORDER BY RND(ID);"

On Error Resume Next
tName = PQryName
'Does query tName exist?  If true, delete it;
test = db.QueryDefs(tName).Name
If Err <> 3265 Then
   docmd.DeleteObject acQuery, tName
End If

Set qd = db.CreateQueryDef(tName, strSQL)

'cleanup
qd.Close
db.Close
Set db = Nothing
RanVaries = &quot;Query &quot; & tName & &quot; was created.  Check database window.&quot;
End Function