why does Random: Rnd() not work for random sort?

[Craig]

Hi

I'm trying to generate some random numbers in a query in order to allow a
random sort of the query using a calculated field called Random: Rnd().

Problem is, the exact same random number is generated for every record in
the query thus negating any ability to create a random sort.

I've also tried Random: Rnd(-1) Random: Rnd(0) and Random: Rnd(1)
as well as other integers with the same result.

I'm using Access 2007.

Any help/thoughts?

thanks,

craig

 

[John W. Vinson]

Hi

I'm trying to generate some random numbers in a query in order to allow a
random sort of the query using a calculated field called Random: Rnd().

Problem is, the exact same random number is generated for every record in
the query thus negating any ability to create a random sort.

I've also tried Random: Rnd(-1) Random: Rnd(0) and Random: Rnd(1)
as well as other integers with the same result.

I'm using Access 2007.

Any help/thoughts?

Access "saves time": when it realizes that Rnd() is called for every record,
it calls it once only and uses the same result for all the examples.

You need to pass the value of a field as an argument. There are two ways to do
this: if you have a number field that you know for certain will never be zero
or less, and will never be null, use Rnd([fieldname]). Or you can use
this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])


John W. Vinson [MVP]

 

[Craig]

Thanks John

I used the autonumber ID field in my table as the fieldname in your first
suggestion. It works perfectly.

c

Hi

I'm trying to generate some random numbers in a query in order to allow a
random sort of the query using a calculated field called Random: Rnd().

Problem is, the exact same random number is generated for every record in
the query thus negating any ability to create a random sort.

I've also tried Random: Rnd(-1) Random: Rnd(0) and Random:
Rnd(1)
as well as other integers with the same result.

I'm using Access 2007.

Any help/thoughts?

Access "saves time": when it realizes that Rnd() is called for every
record,
it calls it once only and uses the same result for all the examples.

You need to pass the value of a field as an argument. There are two ways
to do
this: if you have a number field that you know for certain will never be
zero
or less, and will never be null, use Rnd([fieldname]). Or you can use
this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])


John W. Vinson [MVP]