Sort on Random Number field not working

[gary b]

First post was not complete - trying again. Excuse length
of post.

MAIN form based on CONTESTANTS table
SUBFORM based on HORSE table

Contestants table includes usual demographic info;
Horse table includes list of races, checkboxes to note
entry in a race, competition times, AND a field named
RANDOM [Number: single; default value = Round(((1000*Rnd())
*(10*Rnd())),0)]

I use a query that polls BOTH joined tables and displays
data for selected fields in a form named Race1Editor. If
I right-click on any field header EXCEPT Random, the sub-
menu includes Sort (asc or desc). I cannot sort by the
Random field. I have no clue why not!

I am trying to generate a 'running order' for the
contestants based on the random number. I have
incorporated the Serialize function from Stephan Lebans
into the form -- this numbers each row consecutively.
This gives me the 'running or draw number' for the race
contestants -- except that the form displays the query
results as the data is pulled from the table. The random
number is NOT used as the primary sort field. Bummer!

I have tried to use the OrderBy prop and several other
approaches -- but nothing works. Once (IF) the query
results are sorted according to the random number, the
Serialize function allows me to print the running order
(hiding the random number field). I then want to record
the running order number of the contestants for this and
every race back into my Horse table. [So I can go back to
this race a year later and re-create the running order!]

Anyone wanna take a whack at this one? I'm stumped!

Thanks for the bandwidth. All ideas appreciated!! If you
need more info, please email -- remove NOSPAM from address

 

[John Vinson]

MAIN form based on CONTESTANTS table
SUBFORM based on HORSE table

Contestants table includes usual demographic info;
Horse table includes list of races, checkboxes to note
entry in a race, competition times, AND a field named
RANDOM [Number: single; default value = Round(((1000*Rnd())
*(10*Rnd())),0)]

The trouble is that Access sees that the Rnd() function doesn't
reference any table field, so it "saves time" by calling it only ONCE
before running the query - and uses the same random number on every
record. Bummer!

Here's my getaround:

Put 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])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

 

[gary b]

John... thanks for the reply. In fact, I believe you
posted this code (or something very similar) in response
to another question. I did not see how it applied to me,
but your explanation helps. I'll give it a try.

I've also discovered that my random number is assigned to
each contestant. The contestant then wears THAT number
for their entire life in the dB. I really want
to 'shuffle the deck' every time I query the database for
race participants: race 1, race 2, etc. Else, race
contestants will always stay in the same general order.

Perhaps I could use your code, triggered by a command
button, to generate new random numbers??

This has turned out to be quite a big project for
a 'newbie'.

Anyway, thanks for your many contributions to the group...
and to my little problem!

-----Original Message-----

MAIN form based on CONTESTANTS table
SUBFORM based on HORSE table

Contestants table includes usual demographic info;
Horse table includes list of races, checkboxes to note
entry in a race, competition times, AND a field named
RANDOM [Number: single; default value = Round(((1000*Rnd ())
*(10*Rnd())),0)]

The trouble is that Access sees that the Rnd() function doesn't
reference any table field, so it "saves time" by calling it only ONCE
before running the query - and uses the same random number on every
record. Bummer!

Here's my getaround:

Put 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])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.



.

 

[John Vinson]

I've also discovered that my random number is assigned to
each contestant. The contestant then wears THAT number
for their entire life in the dB. I really want
to 'shuffle the deck' every time I query the database for
race participants: race 1, race 2, etc. Else, race
contestants will always stay in the same general order.

Perhaps I could use your code, triggered by a command
button, to generate new random numbers??

No command button is needed.

Just put the RandNum() function into a Module; compile it; and use it
as the sort field in your Query.