- Local time
- Today, 12:33
- Joined
- Feb 28, 2001
- Messages
- 26,999
Actually, if you are using the Rnd() function, one call to Randomize OUTSIDE the query will nonetheless affect the query too, because this is one of those cases where SQL and VBA use the same Rnd function (from the same library). There IS no SQL "Random" function but there is nothing to stop SQL from calling something that is globally available. After all, you can call a public VBA function from a query - so you can call a public library function as well. Therefore, a Randomize that affects one context will affect BOTH contexts.
In the end analysis, ALL purely mathematical random number generators have a "period" i.e. a number of cycles after which the sequence will repeat itself. Most such random numbers are generated using some variant of Xn+1 = ( a * Xn + b ) mod M and the period is related to the value of M. The first value X0 is the seed. Because this is a modulus function, the number of different random values is 2^M for a binary generator. The Randomize function changes the seed value, i.e. the value X0. In so doing, they merely start the sequence from some other point in the cycle. Some folks would warn against using a seed of 0, but because of that "b" term, there is usually no advantage or disadvantage as to where you start. Note, however, that some home-grown functions could run into issues with poor choices of seed, offset, or multiplier. Therefore, it would be better to use commercial functions rather than to "roll your own" unless you are (a) experimenting or (b) well-versed in this kind of theory.
In the end analysis, ALL purely mathematical random number generators have a "period" i.e. a number of cycles after which the sequence will repeat itself. Most such random numbers are generated using some variant of Xn+1 = ( a * Xn + b ) mod M and the period is related to the value of M. The first value X0 is the seed. Because this is a modulus function, the number of different random values is 2^M for a binary generator. The Randomize function changes the seed value, i.e. the value X0. In so doing, they merely start the sequence from some other point in the cycle. Some folks would warn against using a seed of 0, but because of that "b" term, there is usually no advantage or disadvantage as to where you start. Note, however, that some home-grown functions could run into issues with poor choices of seed, offset, or multiplier. Therefore, it would be better to use commercial functions rather than to "roll your own" unless you are (a) experimenting or (b) well-versed in this kind of theory.