random value generator between 2 dates

[cbrxxrider]

SELECT TOP 1 *
FROM (SELECT *,
RND(ID) AS RandomValue
FROM tblCompletions)


ORDER BY RandomValue

I'm trying to add specific dates to a functioning random value generator and it does not like how I've created it. I'm using short dates.

I've tried this the following and variations of it which gives me a syntax error (missing operator).

SELECT TOP 1 *
FROM (SELECT *,
RND(ID) AS RandomValue
FROM tblCompletions)
WHERE completiondate '02/13/2014' and '02/15/2014'
ORDER BY RandomValue

[orange]

Can you give a few more details about what you have and what you want?
What is your table designs for tblCompletions?

[cbrxxrider]

An example of the table is shown below. I'm trying to add a button, when pushed would give a randomly generated record between the dates 2/13/14 and 2/15/14 or whatever I decide to pull between.

ID	Department	EmployeeID	Goal	CompletionDate	Entered By	Verified By	Comments	Points Earned	Denied By	Denied Reason	Denied Date	Verified Date
265	Revenue Audit	213	Accident free for one month (Department)	3/5/2014	455	455	see above	1				3/5/2014

[June7]

Bing: Access random record select

Review http://www.fontstuff.com/vba/vbatut02.htm

[orange]

How random can it be given your example? 2/13/2014 and 2/15/2014 (You have 2/14/2014 and what else??)

Here's a randomNumber generator I used on a previous project. It should give you soome ideas.

'---------------------------------------------------------------------------------------
' 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 Integer, Hi As Integer) As Integer
   On Error GoTo random_Error
Randomize
randomNumber = Int((Hi - Lo + 1) * Rnd - Lo)

   On Error GoTo 0
   Exit Function

random_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure randomNumber of Module "
End Function

Here is a test routine to show how to call the randomNumber

Sub mytestOfRandomNumber()
    Dim a As Integer
    Dim z As Integer
    Dim i As Integer
    a = 3
    z = 300
    For i = 1 To 10
        Debug.Print randomNumber(a, z)
    Next i
End Sub

Here is the result showing 10 random numbers between 3 and 300

273 
 101 
 23 
 57 
 113 
 180 
 199 
 64 
 187 
 275

You could call this routine passing integer values based on your form.
eg
add a randomNumber of days to your lowest date value using a DateAdd function.

[cbrxxrider]

I could paste all the data but I only gave one example to save space (wasn't a great example of the date of course because this one falls on 3/5/14). I have records with dates ranging from 2/3/14 to present.

I'm just trying to learn how to randomly pull 1 record out of say 10 that fall between 2/13/2014 and 2/15/2014. The expression I currently have will correctly pull a random record but I'm continually incorrectly adding the where statement for the dates I'd like this random pull to fall between.

[cbrxxrider]

Thanks again June7. I've been looking over different articles about random generators and how to pull data between two dates and then trying to combine them. This one might get me there.

[orange]

Did you look at the sample code?
Here's an example using 2 Dates.

Sub ARandomNumberTestWithDates()
Dim LoDate As Date
Dim HiDate As Date
Dim I As Integer

LoDate = #2/10/2014# '<------------Lowest Date
HiDate = #3/25/2014# '<------------Highest Date
I = DateDiff("d", LoDate, HiDate)
Debug.Print "Random date between " & LoDate & "  and  " & HiDate; "  is " & DateAdd("d", LoDate, randomNumber(0, I))

End Sub

I ran the test 4 times with these results

Random date between 10/02/2014 and 25/03/2014 is 12/03/2014
Random date between 10/02/2014 and 25/03/2014 is 15/02/2014
Random date between 10/02/2014 and 25/03/2014 is 22/03/2014
Random date between 10/02/2014 and 25/03/2014 is 21/02/2014