|
Subject:
|
Rnd Function
|
|
Posted By:
|
ghall202
|
Post Date:
|
4/17/2008 7:54:15 AM
|
I'm trying to use the Rnd function to select a certain number of records from a table. Let's say I have 600 records in tblMain and I need to review 50 of these. How would I go about selecting 50 of these at random?
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/17/2008 10:32:11 AM
|
You would need to do something like this:
As far as getting a random number, I would refer to the AutoNumber field, which I am assuming is there.
Then take the MAX() value of the ID field as the upper bound of the random number generator. Use 1 for the lower bound.
Then using a target table to store the individual records as they are pulled (with a delete query to delete all the data before each run), do something like this (psuedo code):
--------------------
Using an input box, take the number of records in the sample (iCount)
iCount = InputBox("Enter a quantity of records to view")
Let's say 50 is entered
Take the highest ID field number (iMax)
iScope is your random number that will call the ID field
Randomize
Do Until i = iCount + 1
iScope = Int((1 * Rnd) + iMax)
On Error Resume Next
Err.Clear
sSQL = "SELECT * FROM tblMain WHERE [IDField] = " & iScope
open recordset
If there is an error, then skip
If Err = 0 Then
Move the record to the target table
End If
i = i + 1
Loop
Then open the resulting table with a form or report.
-------------------------------
Did that help?
This should work. Let me know if iScope keeps returning the same initial number, which it shouldn't do.
mmcdonal
Look it up at: http://wrox.books24x7.com
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/17/2008 10:33:15 AM
|
Critical Change:
Change this section:
If Err = 0 Then
Move the record to the target table
End If
i = i + 1
To this:
If Err = 0 Then
Move the record to the target table
i = i + 1
End If
Sorry.
mmcdonal
Look it up at: http://wrox.books24x7.com
|
