Setting Random Order

[Paul H]

I want a query to pull some records in random order. I would like it to be a different order each time. This is to mix up questions on a test each time it is given. I've tried using the rnd() function, but it gives the same value for each record. So far I haven't found a good way to do this. Any suggestions?

[June7]

I think that has been asked in forum, but finding it again would be a challenge.

Bing: query random records

http://access.mvps.org/access/queries/qry0011.htm

[Dal Jeanis]

Put this code in a standard module (for example, mdlRandomizer).

Option Compare Database
Option Explicit
' Function NewRandom
'
' The input parameter can be any text or number field
' available to the query.
' The value of the parm itself is totally ignored, but it is there
' so that, in a query, the function will be called once for each 
' record and therefore each record will be assigned
' a different random number between 0.0000 and 1.0000
'
'
Public Function NewRandom(ThisParmWillBeIgnored As Variant) As Single
   Static AlreadyRandomized As Boolean
   
   ' The first call (only) will randomize the seed using
   ' the system time
   If Not AlreadyRandomized Then
      Randomize
      AlreadyRandomized = True
   End If
   
   NewRandom = Rnd()
   
End Function

[Paul H]

Dal,

It worked fine.

Thank you for a solution that was easy to implement.

[Paul H]

June,

Your code was almost identical.

Thank you too.