Random Number list without duplicates in a range.

[Guest]

I could use some help with the following issue. I have a form that needs to
generate a random sample. The criteria is that a sample of at least 10 but
no more than 50 needs to be selected (based on pulling 10% of the items to be
audited). 0 cannot be a returned since the recordset starts at 1. This is
selecting items to audit, so there should be no duplicates. Also, when the
result set comes back it would be extremely helpful if it would sort in
accending order. I am doing the best I can with this programming stuff, but
I am definately still learning. Any help would be greatly appreciated.
Below is the code we are currently using.

'generate the number of random numbers needed

iRandomNumberCount = Round((stNumberofDiscrepancies * 0.1), 0)

If Val(iRandomNumberCount) >= 10 And Val(iRandomNumberCount) <= 50
Then
iTotalCount = iRandomNumberCount

ElseIf Val(iRandomNumberCount) < 10 Then
iTotalCount = 10

ElseIf Val(iRandomNumberCount) > 50 Then
iTotalCount = 50

End If

'create the header

If lstRandomNumbers.ListCount = 0 Then
lstRandomNumbers.AddItem "ID Number"

End If

'generate the numbers needed to populate in table

Me.lstRandomNumbers.RowSource = ""


For iCounter = 1 To Val(iTotalCount)

'Generate random number

iRandomNumber = Int(stNumberofDiscrepancies * Rnd)

lstRandomNumbers.AddItem iRandomNumber


Next iCounter


End Sub

 

[John Vinson]

I could use some help with the following issue. I have a form that needs to
generate a random sample.

How about a much simpler solution? Rather than trying to generate a
list of random ID values, just sort the records in random order and
use the Top Values property of the query to return a random shuffled
subset. This set can then be sorted by basing a second query on the
random query.

You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.

John W. Vinson[MVP]