AUTO INSERTING 100 RANDOM NUMBERS 3

[Hiccup]

For a sampling routine I use at work, I've got to create an Access form that contains 100 fields, each field containing a random number between 001 and 100 with no repeats of a number in any of the fields.

I've tried using RANDBETWEEN in the criteria of a query, but with no luck.

Maybe the easiest way is to manually enter the numbers and then randomly sort the fields of the table if that can be done.

I would like to include a cmd button on the form that would reorder the random numbers between 001 and 100 whenever I click on it..

Anyone got a cleaver way to accomplish the above?

Thanks,

Ed "Hiccup" Hicks
ed.hicks@wcg.com

 

[Rick39]

Function Autonum()
Dim MyValue
Dim i as integer

For i = 0 to 100 -1
Randomize ' Initialize random-number generator.
MyValue = Int((100 * Rnd) + 1) ' Generate random value between 1 and 100.
Autonum = Format(MyValue, "000#&quot
'MsgBox Autonum
Next i

End Function

 

[MichaelRed]

To generate the values you note is not really a set of RANDOM values at all, since you use each possible value in the set. It is (perhaps) better described as a random distribution of the value set. This is often refered to as "shuffling", as in the shuffling of the cards done by the dealer in a card game. Using Advanced search with some selected key words (Random, Shuffle) through the various VB and Ms. A. fora should garner quite a few threads detailing various procedures for this.

Unfortunatly, numerous versions are like Rick39's, and fail to fully encompass the necessity of UNIQUE values within the set.

Some others incur the problem of attempting to check each 'new' value, as it is generated, aginst the set of already generated values. This reuslts in routines which take an extremely long time to execute for significant value sets.

The 'proper' approach is to simply generate the list (in your case 1 to 100, paired with a random value (overall range not relevant) and sort the set acording to the random value, but using the ordinal value.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GHolden]

I've done something similar to this to return random records. The only downside is that you would need, in this case to create a table (in this case tbl1to100) with the a field (in this case Number_INT) and populate the table with the numbers 1 to 100.

eg.

Number_INT
1
2
3
4
5.....
99
100

then using the following SQL query...

SELECT tbl1to100.Number_INT
FROM tbl1to100
ORDER BY Rnd(tbl1to100.Number_INT);

the records will be returned in a random order (this easily gets round the problem of duplicate values).

May not be the ideal solution, but might give you some ideas.

There are two ways to write error-free programs; only the third one works.