-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The Randomizer can return duplicate numbers 'cuz it is a singular event
for many items. IOW, each time the randomizer function is run it
doesn't exclude previously selected numbers; therefore, duplicate
numbers can be re-selected by the randomizer. The way I avoided the
duplicate problem was to keep the list of selected numbers in an array.
When the randomizer selects a number, that number is compared to the
array of already selected numbers. If the newly selected number already
exists in the array it is discarded and the process repeated.
Here is the procedure I use. You may use it as a starting point for
your solution.
Sub getRandomNumbers(ByVal lo As Integer, ByVal hi As Integer, _
ByVal toSelect As Integer, result() As Integer)
' Purpose:
' Return an indicated number of unique random numbers
' from a defined population.
' In:
' lo The bottom number in the population
' hi The top number in the population
' e.g: lo = 73, hi = 250
' toSelect The number of items between lo & hi to return.
' Out:
' result() The resulting array of integers
' Created:
' mgf 25may99
' Modified:
' mgf 28mar2001 Changed from a Function to a Sub. Instead
' of returning an array of Booleans, whose
' index is the random number, return an array
' of numbers whose value is the random number.
'
ReDim items(lo To hi) As Boolean
Dim selected As Integer
Dim num As Integer
' Seed the randomizer
Randomize
' Generate the array of unique, random items
Do While selected < toSelect
' Get a number between lo and the hi boundaries
' * From the VBA Help file on Rnd() *
num = Int((hi - lo + 1) * Rnd + lo)
If items(num) = False Then
' Mark the item as selected
items(num) = True
' Keep track of the number of items selected.
selected = selected + 1
' Load the results array
result(selected) = num
End If
Loop
End Sub
' ---- how to call the subroutine
Function testGetRandom()
' test getRandomNumbers subroutine
Const lo As Integer = 25 ' Low boundary of population
Const hi As Integer = 1000 ' High boundary of population
Const itms As Integer = 10 ' Number of items to return
ReDim result(1 To itms) As Integer
getRandomNumbers lo, hi, itms, result()
Dim i As Integer
For i = 1 To itms
Debug.Print result(i)
Next i
End Function
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQUZFO4echKqOuFEgEQIW9ACg13AlDwixwzrXRTtCBUe5wB MI/msAmgLl
+aAok2RaT9sTGRAC2DyCnZF4
=sZfg
-----END PGP SIGNATURE-----
Ellen Manning wrote:
I've got an A2K database with a report that generates any number of
random medical record numbers. The user inputs how many numbers they
want and report uses the Randomizer function found on "The Access Web"
site to generate these numbers. Out of 38000 records, there are 900
unique medical record numbers. I run the randomizer function on a
query of these unique medical record numbers.
The user states there are too many duplications from one report to
another. They consistently ask for 100 numbers. When I run the
reports I see some duplicates on occasion but not enough for me to
think there's a problem with the function.
Anybody had this issue before and if so how did you solve it? Below
is the query and function I use if it's of any help.
Thanks for any help or advice.
Here's my query to return random numbers:
SELECT TOP 100 qselStep3UniqueMRs.MedicalRecordNumber FROM
qselStep3UniqueMRs WHERE Randomizer() = 0 ORDER BY
Rnd(IsNull(qselStep3UniqueMRs.MedicalRecordNumber) * 0 + 1);
Here's the function:
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
Does anybody see a problem
