Insert Random Number

[Locky]

Sorry if this is a repost - my connection went as I was posting the
1st time!

I want to insert a random number, in mod 10, in a field, based on
whether another field is empty or not.

I am using the following query:

UPDATE tbl_Results SET Q11_17 = (Int((100000-50+1)*Rnd()+100000) Mod
10)*10
WHERE Q8_17<>'';

If I run this query, all fields in Q11_17 are updated with the SAME
random number if Q8_17 is not empty.

I want every field in Q11_17 that meets the criteria to have a NEW
random number, so my question is, how do I re-initialize the Rnd
function for each field?

Thanks

[Trevor Best]

Locky wrote:

Sorry if this is a repost - my connection went as I was posting the
1st time!

I want to insert a random number, in mod 10, in a field, based on
whether another field is empty or not.

I am using the following query:

UPDATE tbl_Results SET Q11_17 = (Int((100000-50+1)*Rnd()+100000) Mod
10)*10
WHERE Q8_17<>'';

If I run this query, all fields in Q11_17 are updated with the SAME
random number if Q8_17 is not empty.

I want every field in Q11_17 that meets the criteria to have a NEW
random number, so my question is, how do I re-initialize the Rnd
function for each field?

Thanks

Call a custom function, e.g.

function Random(Max, dummy)
randomize timer
random = rnd() * Max
end function

Send a field from the query as dummy parameter, this will ensure Access
calls the function for each record otherwise it will try to be eficient
and call it once for the entire query and plonk the same result into
each row.

[adam.lock]

Hi

Thanks for such a quick reply, but I am unsure as to how to implement
this in an access query.
Could you give me more info?
Sorry!!

[Trevor Best]

ad*******@gmail.com wrote:

Hi

Thanks for such a quick reply, but I am unsure as to how to implement
this in an access query.
Could you give me more info?
Sorry!!

Something like: update table set field = Random(50)

[c.grimsby]

That'll give every record the same number, Trevor. At least it did in
Access97. I don't think I've tried it since.

Cheap trick: Create an Autonumber field set to random... <Grin>

Or use a function that returns a random number after setting the
randomize value first.

[Trevor Best]

c.*******@worldnet.att.net wrote:

Or use a function that returns a random number after setting the
randomize value first.

See the function 2 posts up, that's what it does, I just forgot the
second parameter on the calling line in the last post.

[c.grimsby]

Trevor Best wrote:

c.*******@worldnet.att.net wrote:

Or use a function that returns a random number after setting the
randomize value first.

See the function 2 posts up, that's what it does, I just forgot the
second parameter on the calling line in the last post.

Indeed, except you don't want to do the "Randomize Timer" each pass
through the function. You only want to do it _once_.

[Trevor Best]

c.*******@worldnet.att.net wrote:

Trevor Best wrote:

c.*******@worldnet.att.net wrote:

Or use a function that returns a random number after setting the
randomize value first.

See the function 2 posts up, that's what it does, I just forgot the
second parameter on the calling line in the last post.

Indeed, except you don't want to do the "Randomize Timer" each pass
through the function. You only want to do it _once_.

I've not tested, what is the overhead of that?

[Chuck Grimsby]

Sorry for the long delay in replying. It's been a week!

The problem with calling Randomize on each pass through the function
has to do with the way the "Random Number Generator" works. In fact,
it's not a generator at all, it's more like a lookup table. The
Randomize statement sets the initial lookup position, then each Random
call reads the value from the table and advances the "pointer" one
record forward.

Using the (optional) setting for the Randomize statement (in this case
Timer), tells the the Random Number Generator at what position to start
at. Giving it the same "time" (for simplicity's sake) re-sets the RNG
back to the same point over and over due to the speed at which queries
run at, thus the function will return the same "random" number over and
over.

Soneone in this newsgroup a while back posted one solution to this
problem which used a STATIC variable to help the function determine if
the Randomize statement had been used before or not, and if it hadn't
then it called the Randomize statement (using Timer). I remember
trying it, however the only way _I_ could get it to work was to call
the function before I ran the query, then let the query do it's thing.
I was doing the same thing using two seperate functions, so I didn't
save the code.