Query Random Date

[Grant Shea]

I am having difficulty with the RND function. I have a database that has thousands of questions and answers some of these questions and answers fit the same theme and were all used on the same date. I would like to create a query that returns all the questions and answers from a random date. Each time I run the query it should return a different date. I can't figure out how to make this work.

Also this in tutorials was not helpful.

 SELECT Questions.Round, Questions.Question, Questions.Answer, Questions.[Date Used], Questions.[Question Order], Questions.Field1, Questions.NotesFROM Questions
WHERE (((Questions.Round)="3 - Theme") AND ((Questions.[Date Used])=RND(Questions.[ID Number])));

[orange]

It would appear you missed this in the tutorial:

20 Randomize 'must use this to getnew random numbers


If this is related to your first post, there is no need to start a new thread.

Here is an article you may find helpful.

More info on Randomize

Here is a random number function with test routines for Random numbers and Random Dates.

'---------------------------------------------------------------------------------------
' 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

Test Routine for random number

'---------------------------------------------------------------------------------------
' 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
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

Generate a random date test

Sub ARandomNumberTestWithDates()
      Dim LoDate As Date
      Dim HiDate As Date
      Dim i As Long

10    LoDate = #3/25/2014#
20    HiDate = #7/25/2015#
30    i = DateDiff("d", LoDate, HiDate)
40    Debug.Print "Random date between " & LoDate & "  and  " & HiDate; "  is " & DateAdd("d", LoDate, randomNumber(0, i))

End Sub

Results of repeated runs of the random Date test
Random date between 25-Mar-2014  and  25-Jul-2015  is 18-Apr-2014
Random date between 25-Mar-2014  and  25-Jul-2015  is 28-Jul-2014
Random date between 25-Mar-2014  and  25-Jul-2015  is 12-Nov-2014
Random date between 25-Mar-2014  and  25-Jul-2015  is 29-Jun-2014
Random date between 25-Mar-2014  and  25-Jul-2015  is 12-Feb-2015
Random date between 25-Mar-2014  and  25-Jul-2015  is 10-Feb-2015
Random date between 25-Mar-2014  and  25-Jul-2015  is 22-Oct-2014
Random date between 25-Mar-2014  and  25-Jul-2015  is 03-Dec-2014
Random date between 25-Mar-2014  and  25-Jul-2015  is 05-Oct-2014
Random date between 25-Mar-2014  and  25-Jul-2015  is 05-Apr-2014
Random date between 25-Mar-2014  and  25-Jul-2015  is 26-May-2015
Random date between 25-Mar-2014  and  25-Jul-2015  is 19-May-2015

Results from 2 iterations of the Random number test:

First run:
1   104
2   46
3   238
4   40
5   257
6   281
7   131
8   282
9   48
10   13


Second run:
1   275
2   258
3   91
4   216
5   138
6   93
7   70
8   137
9   235
10   21