Oder by Rnd() isn't random

[Andrew Banks]

I'm trying to order a recordset randomly. I've tried the following

ORDER BY Rnd(ProdID) - ProdID is my primary key

I've read that this will be random but will always return the same random
order. I've also read that using the following should resolve this

ORDER BY Rnd(Timer()) - this still gives me the same problem

I've only got 16 recors in the DB at the moment if this is relevant.

Can anyone help me return my recordset in a new random order every time
EVERY time

Thanks in advance

[Aaron Bertrand - MVP]

Rnd() is only random based on the seed. ProdID is going to be a constant
seed, and I don't think timer() is going to vary enough between refreshes to
see a difference.

Why don't you store the result in getrows, and then iterate through the
array in using a random order?

A

"Andrew Banks" <ba****@nojunkblueyonder.co.uk> wrote in message
news:C8*********************@news-text.cableinet.net...

I'm trying to order a recordset randomly. I've tried the following

ORDER BY Rnd(ProdID) - ProdID is my primary key

I've read that this will be random but will always return the same random
order. I've also read that using the following should resolve this

ORDER BY Rnd(Timer()) - this still gives me the same problem

I've only got 16 recors in the DB at the moment if this is relevant.

Can anyone help me return my recordset in a new random order every time
EVERY time

Thanks in advance

[Harag]

Would help better if you say what DB your using...
if your using SQL Server try

ORDER BY NEWID()

HTH
Al
On Fri, 14 Nov 2003 17:19:30 GMT, "Andrew Banks"
<ba****@nojunkblueyonder.co.uk> wrote:

I'm trying to order a recordset randomly. I've tried the following

ORDER BY Rnd(ProdID) - ProdID is my primary key

I've read that this will be random but will always return the same random
order. I've also read that using the following should resolve this

ORDER BY Rnd(Timer()) - this still gives me the same problem

I've only got 16 recors in the DB at the moment if this is relevant.

Can anyone help me return my recordset in a new random order every time
EVERY time

Thanks in advance

[Andrew Banks]

Very good point!

I'm using and Access DB and I'm afraid I have no option to change to SQL
Server
"Harag" <ha***@REMOVETHESECAPITALSsofthome.net> wrote in message
news:kg********************************@4ax.com...

Would help better if you say what DB your using...
if your using SQL Server try

ORDER BY NEWID()

HTH
Al
On Fri, 14 Nov 2003 17:19:30 GMT, "Andrew Banks"
<ba****@nojunkblueyonder.co.uk> wrote:

I'm trying to order a recordset randomly. I've tried the following

ORDER BY Rnd(ProdID) - ProdID is my primary key

I've read that this will be random but will always return the same random
order. I've also read that using the following should resolve this

ORDER BY Rnd(Timer()) - this still gives me the same problem

I've only got 16 recors in the DB at the moment if this is relevant.

Can anyone help me return my recordset in a new random order every time
EVERY time

Thanks in advance

[Carl Johansen]

What if you declare a function like this:

Public Function GetRandom(ByVal dummy As Long) As Double
Static blnRandomized As Boolean
If blnRandomized <> True Then
blnRandomized = True
Randomize
End If
GetRandom = Rnd()
End Function

Then add a column to your query, eg GetRandom([ProdID]) AS SortOrder
and ORDER BY SortOrder

The dummy arg is there because I have found that if Access sees a function
with no args it assumes that it always returns the same result and so it
just evaluates it once and uses that result for every row it returns.

HTH
"Andrew Banks" <ba****@nojunkblueyonder.co.uk> wrote in message
news:Df*******************@news-text.cableinet.net...

Very good point!

I'm using and Access DB and I'm afraid I have no option to change to SQL
Server
"Harag" <ha***@REMOVETHESECAPITALSsofthome.net> wrote in message
news:kg********************************@4ax.com...

Would help better if you say what DB your using...
if your using SQL Server try

ORDER BY NEWID()

HTH
Al
On Fri, 14 Nov 2003 17:19:30 GMT, "Andrew Banks"
<ba****@nojunkblueyonder.co.uk> wrote:

I'm trying to order a recordset randomly. I've tried the following

ORDER BY Rnd(ProdID) - ProdID is my primary key

I've read that this will be random but will always return the same randomorder. I've also read that using the following should resolve this

ORDER BY Rnd(Timer()) - this still gives me the same problem

I've only got 16 recors in the DB at the moment if this is relevant.

Can anyone help me return my recordset in a new random order every time
EVERY time

Thanks in advance

[Indigo Montoya]

Its a little messy but you could do the following:

1) Create an access module
2) Paste the following:

Public Function NewRnd(ProdID As Integer) As Integer
Dim intRnd As Integer
intRnd = Rnd * 1000
NewRnd = intRnd
End Function

3) Using the following in your Order by clause:
ORDER BY NewRnd([prodID])
"Carl Johansen" <ca**********@btinternet.com> wrote in message news:<bp**********@titan.btinternet.com>...

What if you declare a function like this:

Public Function GetRandom(ByVal dummy As Long) As Double
Static blnRandomized As Boolean
If blnRandomized <> True Then
blnRandomized = True
Randomize
End If
GetRandom = Rnd()
End Function

Then add a column to your query, eg GetRandom([ProdID]) AS SortOrder
and ORDER BY SortOrder

The dummy arg is there because I have found that if Access sees a function
with no args it assumes that it always returns the same result and so it
just evaluates it once and uses that result for every row it returns.

HTH
"Andrew Banks" <ba****@nojunkblueyonder.co.uk> wrote in message
news:Df*******************@news-text.cableinet.net...

Very good point!

I'm using and Access DB and I'm afraid I have no option to change to SQL
Server
"Harag" <ha***@REMOVETHESECAPITALSsofthome.net> wrote in message
news:kg********************************@4ax.com...

Would help better if you say what DB your using...
if your using SQL Server try

ORDER BY NEWID()

HTH
Al
On Fri, 14 Nov 2003 17:19:30 GMT, "Andrew Banks"
<ba****@nojunkblueyonder.co.uk> wrote:

>I'm trying to order a recordset randomly. I've tried the following
>
>ORDER BY Rnd(ProdID) - ProdID is my primary key
>
>I've read that this will be random but will always return the same random >order. I've also read that using the following should resolve this
>
>ORDER BY Rnd(Timer()) - this still gives me the same problem
>
>I've only got 16 recors in the DB at the moment if this is relevant.
>
>Can anyone help me return my recordset in a new random order every time
>EVERY time
>
>Thanks in advance
>

[Bob Barrows]

Indigo Montoya wrote:

Its a little messy but you could do the following:

1) Create an access module
2) Paste the following:

Public Function NewRnd(ProdID As Integer) As Integer
Dim intRnd As Integer
intRnd = Rnd * 1000
NewRnd = intRnd
End Function

3) Using the following in your Order by clause:
ORDER BY NewRnd([prodID])

That will not work in queries executed from non-Access applications. Custom
functions in Access modules are not accessible to the Jet engine when Access
is not involved in the process.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"