> I'm trying to do a search under a set criteria followed by a selection of
random entries meeting this criteria. But I don't seem to be able to
achieve this.
The idea being to search on say subject and then select a random set of
records meeting that subject.
Any ideas or thought would be helpful. I'm using Access XP to try this
out.
I'm not a database person myself, so I'll leave the specifics of
implementing this idea to you. What I am thinking is to find out how many
records were return from you search, assign the record numbers to an array
of integers, shuffle that array of record numbers into a random order, and
then pick out the first X number of record numbers (you decide on how many
random entries you want) in order to index back into the set of records that
was returned from you search. For example, if your search returned, say,
record numbers 2, 19, 20, 39 and 50, then set up this array
Dim NumberOfReturnedRecords As Long
Dim RecNums() As Long
' Search your database and get the returned records
NumberOfReturnedRecords = <<RecordCount, 5 for this example>>
ReDim RecNums(1 To NumberOfReturnedRecords)
RecNums(1) = 2
RecNums(2) = 19
RecNums(3) = 20
RecNums(4) = 39
RecNums(5) = 50
then shuffle the contents around using the subroutine found at the end of
this message
RandomizeArray RecNums
and, finally, use the first, say, 3 entries (for this example) to obtain the
record numbers of 3 random items from your search set.
Rick - MVP
The following is a generalized "shuffling" routine that I've posted in the
past. Give it an array of elements and it will put them in random order and
return the randomized elements back in the original array that was passed to
it. It only visits *each* array element *once* so it is quick. The code
takes care of running the Randomize statement one time only (which is all
that is necessary).
Sub RandomizeArray(ArrayIn As Variant)
Dim X As Long
Dim RandomIndex As Long
Dim TempElement As Variant
Static RanBefore As Boolean
If Not RanBefore Then
RanBefore = True
Randomize
End If
If VarType(ArrayIn) >= vbArray Then
For X = UBound(ArrayIn) To LBound(ArrayIn) Step -1
RandomIndex = Int((X - LBound(ArrayIn) + 1) * _
Rnd + LBound(ArrayIn))
TempElement = ArrayIn(RandomIndex)
ArrayIn(RandomIndex) = ArrayIn(X)
ArrayIn(X) = TempElement
Next
Else
'The passed argument was not an array
'Put error handler here, such as . . .
Beep
End If
End Sub