need random number routine

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

that when called will generate a random number between 100,000 and 999,999
and place it in a table. code must check this existing random number table to
make sure there is no duplicate.

thanks, rob
 
That is a lot of number.

When I need to select randon records in Access I add an Autonumber with the
increment set for random. Then I query the number of records I need from the
top either ascending or descinding.
 
that when called will generate a random number between 100,000 and 999,999
and place it in a table. code must check this existing random number table to
make sure there is no duplicate.

thanks, rob
Click to expand...

ok... just off the top of my head, no guarantees:

Public Function RndID(vIgnore As Variant) As Long
Static bRandomized As Boolean
Dim bUnique As Boolean
Dim lngAns As Long
If Not bRandomized Then
Randomize ' initialize the random number generator once
bRandomized = True
End If
Do Until bUnique
lngAns = CLng(900000 * Rnd()) + 100000
bUnique = IsNull(DLookUp("[fieldname]", "tablename]", _
"[fieldname] = " & lngAns)
Loop
rndID = lngAns
End Function

Of course if you have 900000 records in the table this will never
exit... the solution of this flaw is left as an exercise.


John W. Vinson[MVP]
 
Hi,


Other alternatives.

Generate all the values in a table. Take one record, at random, Delete it
(or flag a field at that effect), after having put it in another table.
Continue to take another one (among those not flagged, if you use a flag),
as required.

Moving, deleting, or even creating tons of new records is relatively slow.
So...


if you use a "flag", assuming the flag is a LONG, nullable field, you can
start with all records with a NULL under it.

MyRandom, Flag ' fields name


The goal is to use the primary key value of the associated record (from
another table) under the column Flag to indicate that the random value is
'taken'. Doing so, you don't delete anything, and you don't have to check
for duplicated value among all other fields (which is easy if you have an
index), but just have to check Flag IS NULL.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top