Randomize statement

[MMesarch]

HI,
I am pulll records from a ACCESS database for display on the web. I have 20
records but want to randomly display 10. On the webside of things (using
ASP) I am planning on reading on the first 10 records from the database.

So I thought I could create a query where I create an id that uses the Rnd
function to create an ID I can sort from the web call.

I realize I have to use the Randomize statement to create the different
sequence for each call. But I cannot figure out how to use the Randomize
statement.

I tried creating a Public function that used the Radnomized Statement and
then use that in the query to create the new ID. But I get an error out on
the web that says it does not recognize the public function.

How can I just apply the Randomize statement to the query when it loads (on
each call) and then just use the Rnd function itself in the equation.

I almost got this to work by just having randomize hanging outside of
procedure, but this is not really right.

THanks.

 

[Marshall Barton]

I am pull records from a ACCESS database for display on the web. I have 20
records but want to randomly display 10. On the webside of things (using
ASP) I am planning on reading on the first 10 records from the database.

So I thought I could create a query where I create an id that uses the Rnd
function to create an ID I can sort from the web call.

I realize I have to use the Randomize statement to create the different
sequence for each call. But I cannot figure out how to use the Randomize
statement.

I tried creating a Public function that used the Radnomized Statement and
then use that in the query to create the new ID. But I get an error out on
the web that says it does not recognize the public function.

How can I just apply the Randomize statement to the query when it loads (on
each call) and then just use the Rnd function itself in the equation.

I almost got this to work by just having randomize hanging outside of
procedure, but this is not really right.

I don't know from ASP so this could be out in left field.

In an Access/Jet query, a constant field expression will
only be evaluated once. So, you could create a little
public function to do the Randomize statement:

Public Function DoRandomize(x As Integer) As Variant
Randomize
End Function

Then the query could be something like:

SELECT TOP 10 DoRandomize(1) As Junk, otherfields
FROM table
QRDER BY Rnd([any positive number field])

 

[Brian]

The problem when using external calls (e.g. ASP) is that only native JET
functions are available, not all the Access functions, and definitely not a
custom function, such as you would need to call the Randomize statement.

However, you can make up your own Randomize-type functionality by
multiplying the Rnd by, perhaps, the Second, Minute from the current time. It
could look something like this (assuming that Rnd works via Jet/ASP):

Int((Rnd(1) * Second(Now)))

Although the actual Rnd will be the same each time, the Second(Now()) will
be different, so the product will be effectively random. Just multiply by a
figure that will give you the correct range of random numbers, then take an
Int to drop the decimal places.

 

[MMesarch]

I tried this two ways and it did not work.
I first tried putting both of these pieces (function and the SQL) in the ASP
and it did not work. IT said it could not find the DoRandomize function.

I think tried putting the function in a model inside ACCESS and the same
result. It cannot see the function.

I also tried the idea that Brian gave in the other response and it did not
create a random set when called. The problem seems to be all the fields get
the same time also so its just like multiplying everything by a constant.

Any more thoughts??? Thanks for the help so far.
--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

I am pull records from a ACCESS database for display on the web. I have 20
records but want to randomly display 10. On the webside of things (using
ASP) I am planning on reading on the first 10 records from the database.

So I thought I could create a query where I create an id that uses the Rnd
function to create an ID I can sort from the web call.

I realize I have to use the Randomize statement to create the different
sequence for each call. But I cannot figure out how to use the Randomize
statement.

I tried creating a Public function that used the Radnomized Statement and
then use that in the query to create the new ID. But I get an error out on
the web that says it does not recognize the public function.

How can I just apply the Randomize statement to the query when it loads (on
each call) and then just use the Rnd function itself in the equation.

I almost got this to work by just having randomize hanging outside of
procedure, but this is not really right.

I don't know from ASP so this could be out in left field.

In an Access/Jet query, a constant field expression will
only be evaluated once. So, you could create a little
public function to do the Randomize statement:

Public Function DoRandomize(x As Integer) As Variant
Randomize
End Function

Then the query could be something like:

SELECT TOP 10 DoRandomize(1) As Junk, otherfields
FROM table
QRDER BY Rnd([any positive number field])

 

[MMesarch]

See response to Marshall... IT did not work It seemed that the same second
was being multiplied by all the numbers.

 

[Marshall Barton]

I tried this two ways and it did not work.
I first tried putting both of these pieces (function and the SQL) in the ASP
and it did not work. IT said it could not find the DoRandomize function.

I think tried putting the function in a model inside ACCESS and the same
result. It cannot see the function.

I also tried the idea that Brian gave in the other response and it did not
create a random set when called. The problem seems to be all the fields get
the same time also so its just like multiplying everything by a constant.

I think that's saying I really am out in left field :-(

If you are not using a Jet backend db, then what I said
before will never work.