rnd giving same results every time

[Boomshank]

I have a table called 'MovieDatabase' which lists all of my movies, i am trying to create a query that when run selects a random film from the list.

The query i have set up does select a different movie each time i run it but the problem is after i close the database down. When i re-open the database and run the query it selects the same movies.

e.g. i open the database and run the query three times, my results are

death proof
bowfinger
the davinci code

I close the database, open it again and run the query another three times, my results are

death proof
bowfinger
the davinci code

The SQL for my query is:

SELECT TOP 1 MovieDatabase.title
FROM MovieDatabase
ORDER BY Rnd(Rnd(ID)) DESC;

ID is my primary key

Does anyone have any idea's how i can overcome this?

 

[DCrake]

Rnd works on the system clock and does not always create random lists. Take a look at randomise

 

[boblarson]

Rnd works on the system clock and does not always create random lists. Take a look at randomise

It's not that RND doesn't work but you need to use Randomize to set up RND so that it will work. They work together.

 

[Boomshank]

Thanks for all your held, i ended up resolving it with VBA using the following code

Sub RandomMovie()
    Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim strTableName As String
    strSQL = "SELECT MovieDatabase.Title, MovieDatabase.Rating " & _
             "INTO tblRandom " & _
             "FROM MovieDatabase;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblRandom")
    Set fld = tdf.CreateField("RanNum", dbSingle)
    tdf.Fields.Append fld
    Set rst = db.OpenRecordset("tblRandom", dbOpenTable)
    rst.MoveFirst
    Do
        Randomize
        rst.Edit
            rst![RandomNumber] = Rnd()
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Set rst = Nothing
    
    strTableName = "tblRandom"
    strSQL = "SELECT TOP 1 MovieDatabase.Title, MovieDatabase.Rating " & _
             "FROM tblRandom " & _
             "ORDER BY tblRandom.RanNum;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub