By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Insert Random Number

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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.
Nov 13 '05 #2

P: n/a
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!!

Nov 13 '05 #3

P: n/a
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)
Nov 13 '05 #4

P: n/a
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.

Nov 13 '05 #5

P: n/a
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.
Nov 13 '05 #6

P: n/a

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_.

Nov 13 '05 #7

P: n/a
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?
Nov 13 '05 #8

P: n/a
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.

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.