how can I select a random record for a report?

[Guest]

I have a table with between 50 and 75 records. I need to pick about 40 of
those records at random to fill a report and this process will be repeated
regularly. (Imagine generating Bingo cards) How can I set up a query or
some function to select records at random?

 

[Arvin Meyer [MVP]]

I have a table with between 50 and 75 records. I need to pick about 40 of
those records at random to fill a report and this process will be repeated
regularly. (Imagine generating Bingo cards) How can I set up a query or
some function to select records at random?

Create a function in a standard module:

Function GetRandomNumber(varIn As Variant) As Double
Randomize Timer
GetRandomNumber = Rnd(1)
End Function

In your query, create an expression passing any field to this function (the
value is not used, but it is necessary to have the function evaluated once
for each row):

SELECT TOP 40 TableName.*
FROM TableName
ORDER BY GetRandomNumber([PrimaryKey]);