Random

[Max]

Thanks to Joe Foster, Below is a code he wrote. My question is:

How would I modify his code to promet a user to enter the random number of
record he/she would like to get?

In the below code it was hard coded to be a 100 recrod.

I used his following code:
(Q) How can I return Random records from a table?

(A) Paste the following function in a new module.

'************ Code Begin ***********
'Code courtesy of
'Joe Foster
Function Randomizer () As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize : AlreadyDone = True
Randomizer = 0
End Function
'************ Code End *************
Now to get 100 questions picked at random:

select top 100 mytable.*from mytable
where randomizer() = 0
order by rnd(isnull(mytable.question) * 0 + 1)

 

[Douglas J. Steele]

Assuming that by "enter the random number of record" you mean that you want
to let them specify how many records to return, you'd have to change the SQL
associated with the query they're going to run.

For the sake of argument, let's say you've saved that SQL as query
qryRandom.

The following code will let you change it:

Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String

strPrompt = InputBox ( _
"How many records do you want to return?", _
"Random Records")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select top " & strPrompt & " mytable.* " & +
"from mytable " & _
"where randomizer() = 0 " & _
"order by rnd(isnull(mytable.question) * 0 + 1)"
Set qdfCurr = CurrentDb().QueryDefs("qryRandom")
qdfCurr.SQL = strSQL
End If
End If

 

[Max]

it gives me an error? Compile error. in query expression
'(((Randomizer())=0))'.

 

[Max]

How would I put the sql in my query That's what I have now :

SELECT TOP 22 Step9_TMGNYC.*
FROM Step9_TMGNYC
WHERE (((Randomizer())=0))
ORDER BY Rnd(IsNull(Step9_TMGNYC.PROVNUM)*0+1);

And That's what I have in my Module:

Option Compare Database
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String

strPrompt = InputBox( _
"How many records do you want to return?", _
"Random Records")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select top " & strPrompt & " RandomQ.* " & "from RandomQ
" & _
"where randomizer() = 0 " & _
"order by rnd(isnull(RandomQ.PROVID) * 0 + 1)"
Set qdfCurr = CurrentDb().QueryDefs("RandomQ")
qdfCurr.SQL = strSQL
End If
End If

 

[Douglas J. Steele]

strSQL = "SELECT TOP " & strPrompt & " Step9_TMGNYC.* " &_
"FROM Step9_TMGNYC " & _
"WHERE (((Randomizer())=0)) " & _
"ORDER BY Rnd(IsNull(Step9_TMGNYC.PROVNUM)*0+1)"

 

[Max]

Thanks a lot douglas. It worked fine.

strSQL = "SELECT TOP " & strPrompt & " Step9_TMGNYC.* " &_
"FROM Step9_TMGNYC " & _
"WHERE (((Randomizer())=0)) " & _
"ORDER BY Rnd(IsNull(Step9_TMGNYC.PROVNUM)*0+1)"