-----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