How do I assign random numbers to a table in Access 2002?

[Guest]

I'm trying to assign random numbers to a table in access for a sweepstakes.
Anyone know how it can be done? Thanks!

 

[Wayne Morgan]

I'm not sure about 2002, but the 2003 version offers Random as one of the
options for an autonumber. Another way would be the example below. It was
placed in the BeforeUpdate event of a form.

If Me.NewRecord Then
Randomize
'Make sure the number doesn't exist. If it does, try again.
Do
sglTestNumber = Rnd
Loop Until IsNull(DLookup("[Lottery Tie Breaker]", "tblBUMs", "[Lottery
Tie Breaker]=" & sglTestNumber))
Me.txtLotteryTieBreaker = sglTestNumber
End If

This will give a number >=0 and <1. If you want a different range of
numbers, that can be done also. The formula is:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

This will produce random integers between the upper and lower bound.

 

[James Hahn]

I suspect that what you are actually trying to do is to fill a list of
records with unique numbers from a range of numbers, arranged in a random
sequence. For instance, you want to fill 43 records in a table with the
numbers 1 to 43, assigned randomly.
See, for instance:
http://personal-computer-tutor.com/abc4/v35/ray35.htm
(Excel, but the code will be the same)
http://www.fontstuff.com/vba/vbatut02.htm
(Different problem, but similar technique)

 

[Ken Snell [MVP]]

Yes, ACCESS 2002 has the Random option for autonumber field in a table.

--

Ken Snell
<MS ACCESS MVP>

I'm not sure about 2002, but the 2003 version offers Random as one of the
options for an autonumber. Another way would be the example below. It was
placed in the BeforeUpdate event of a form.

If Me.NewRecord Then
Randomize
'Make sure the number doesn't exist. If it does, try again.
Do
sglTestNumber = Rnd
Loop Until IsNull(DLookup("[Lottery Tie Breaker]", "tblBUMs", "[Lottery
Tie Breaker]=" & sglTestNumber))
Me.txtLotteryTieBreaker = sglTestNumber
End If

This will give a number >=0 and <1. If you want a different range of
numbers, that can be done also. The formula is:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

This will produce random integers between the upper and lower bound.

--
Wayne Morgan
MS Access MVP

I'm trying to assign random numbers to a table in access for a
sweepstakes.
Anyone know how it can be done? Thanks!

 

[Douglas J. Steele]

Random autonumbers have been around for a long time (they're in Access 97).
When did Replication get introduced? Random autonumbers are a requirement
for that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm not sure about 2002, but the 2003 version offers Random as one of the
options for an autonumber. Another way would be the example below. It was
placed in the BeforeUpdate event of a form.

If Me.NewRecord Then
Randomize
'Make sure the number doesn't exist. If it does, try again.
Do
sglTestNumber = Rnd
Loop Until IsNull(DLookup("[Lottery Tie Breaker]", "tblBUMs", "[Lottery
Tie Breaker]=" & sglTestNumber))
Me.txtLotteryTieBreaker = sglTestNumber
End If

This will give a number >=0 and <1. If you want a different range of
numbers, that can be done also. The formula is:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

This will produce random integers between the upper and lower bound.

--
Wayne Morgan
MS Access MVP

I'm trying to assign random numbers to a table in access for a
sweepstakes.
Anyone know how it can be done? Thanks!