424,652 Members | 1,995 Online
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
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... 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 therandomize value first.See the function 2 posts up, that's what it does, I just forgot thesecond 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.