Rnd() NOT RANDOM

[TKMIA10]

I am trying to create a simple query for an exercise database. The query is supposed to first prompt the user to input a specific body part, and then the query is supposed to randomly select 5 exercises from the "Exercise Directory" table for that body part. The SQL I currently have is:

SELECT TOP 5 *
FROM [Exercise Directory]
WHERE [Exercise Directory].[Body Part]=Enter
ORDER BY Rnd(Len([Exercise]));

Every time I open the workbook and put in the code for say, biceps (every day is arm day), the query spits out the same 5 results in the same order. If I re-run the query, it spits out 5 "new" results, but these are the same results I get every time if I run the query twice, and always in the same order.

I feel like this should be an incredibly simple operation, so I must be missing something obvious?

[orange]

You have to use RANDOMIZE to ensure random (not repeated) values.
See this article for more info

[TKMIA10]

I had found that previously and tried creating the Macro verbatim and created the "RandomValue: Rnd(Abs[ID])" expression in Design view. I even stripped out the prompt to select a specific body part. The adjusted SQL code was:
SELECT [Exercise Directory].[ID], [Exercise Directory].[Exercise], [Exercise Directory].[Body Part], [Exercise Directory].[Attachment], Rnd(Abs([ID])) AS RandomValue
FROM [Exercise Directory]
ORDER BY Rnd(Abs([ID]));


However, the same issue persists. Every time I open the database again, I get the same "random" list in the exact same order.

[orange]

I do not use macros. You missed the point that you must include Randomize in your code.

Here is a random number generating function

'---------------------------------------------------------------------------------------
' Procedure : randomNumber
' Author    : Jack
' Created   : 11/18/2010
' Purpose   : To Generate Random numbers between and including a range of numbers.
'Lo and Hi are the lowest and highest random numbers you wish to generate.
'
'The Randomize keyword is critical to getting different results for each Access session.
'=======================================================================================
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function randomNumber(Lo As Long, Hi As Long) As Long
10       On Error GoTo randomNumber_Error

30    Randomize
40    randomNumber = Int((Hi - Lo + 1) * Rnd + Lo)

50       On Error GoTo 0
60       Exit Function

randomNumber_Error:

70        MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure randomNumber of Module AccessMonster"

End Function

Here is a test routine

'---------------------------------------------------------------------------------------
' Procedure : mytestOfRandomNumber
' Author    : Jack
' Date      : 11/18/2010
' Purpose   : Create 10 random numbers between 3 and 300
'---------------------------------------------------------------------------------------
'
Sub mytestOfRandomNumber()
          Dim a As Long
          Dim z As Long
          Dim i As Integer
   On Error GoTo mytestOfRandomNumber_Error

10        a = 3
20        z = 300
30        For i = 1 To 10
40            Debug.Print i & "   " & randomNumber(a, z)
50        Next i

   On Error GoTo 0
   Exit Sub

mytestOfRandomNumber_Error:

    MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure mytestOfRandomNumber of Module AccessMonster"
End Sub

Results: 4 executions gave these results

1 128
2 49
3 266
4 96
5 168
6 250
7 144
8 244
9 163
10 265


1 237
2 169
3 154
4 119
5 6
6 286
7 119
8 244
9 87
10 121


1 75
2 142
3 160
4 119
5 242
6 250
7 6
8 63
9 33
10 280


1 35
2 221
3 39
4 149
5 116
6 56
7 237
8 52
9 55
10 45

Good luck.