Random Number

Blaise · August 17th, 2003, 10:09 PM

Hi there, I would like to generate a random number between 10000 and 99999 (5 digits), I'm trying to write a SQL query that would insert a random value into each record.

I've tried using RANDBETWEEN(10000, 99999) but I always get an error "Undefined function 'randbetween' in expression" I found this function in the Access help though!?

Any help would be appreciated.

Cheers,

Blaise

Bob Bedell · August 18th, 2003, 12:40 AM

Hi Blaise,

You can use the Visual Basic functions Rnd() and Int() with the Randomize statement. To test, create a simple table:

Table1
  Field1 Number (Primary Key)
  Field2 Text

Create a form with two controls:

txtField2 (unbound)
cmdInsert

Paste the following into the form module. You must have a reference set to the DAO 3.x library. This code will insert a random value within your required range into the primary key field:

Private Sub cmdInsert_Click()

    Dim db As DAO.Database
    Dim strSQL As String

    Randomize

    Set db = CurrentDb

    strSQL = "INSERT INTO Table1 "
    strSQL = strSQL & "(Field1, Field2)"
    strSQL = strSQL & " VALUES ("
    strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() + 10000))
    strSQL = strSQL & ",""" & Me!txtField2 & """);"

    Debug.Print strSQL

    db.Execute strSQL

    db.Close

End Sub

Open the form, add some text to txtField2, press cmdInsert. A new row with a random primary key value should appear in Table1. I added a Debug.Print statement so you can set a break point and examine the SQL string generated in the Immediate Window (Ctrl-G).

Function Rnd returns a Single random number that is always in the range 0 <= Rnd < 1. To return a range of Integers, add the Int function and a "scaling factor" which is the multiplier of Rnd(). Then shift the range of numbers produced by adding a "shift adjustment" (+10000). Be aware that Rnd() actually generates a sequence of pseudo-random numbers that repeats itself each time a program is run. You can condition Rnd() to produce a different sequence of random numbers by adding the Randomize statment. Randomize "seeds" Rnd() by supplying the random number generator with an initial number that receives its value from the system clock.

I generated several hundred records and the value range checks out. Of course, I wouldn't recommend using this approach to actually generate primary key values. That's the Jet engine's job (using autonumber fields). I simply wanted to ensure that unique values were generated for demonstration purposes.

HTH,

Bob

Steven · August 21st, 2003, 11:08 PM

While that might work fine, it has been said that the Random function in VBA is not as random as it should be. So if you intend to have real random numbers, you should use a standard random number generating algorithm, such as the one mentioned in Algorithm AS 183 Appl. Statist. (1982) vol.31, no.2

Now that doesn't quite give you an algorithm that you can use in Access, so I'll post the code that does have it work for Access.

This function assumes you have a field in your table called "Random", and it generates enough numbers randomly to fill the table (ie: if you have 500 records in your table, it generates 1-500) If you want it to generate them starting from 10000, just add 9999 to the number.

So here goes:

Code:

Public Function rando2()


Dim dbs As Database, rstYourRecordSet As Recordset
Dim rsCount As Long
Dim x() As Double
Set dbs = CurrentDb
Set rstYourRecordSet = dbs.OpenRecordset("YourTable")
dbs.Execute ("UPDATE YourTable SET random = Null")
rsCount = rstYourRecordSet.RecordCount
ReDim Preserve x(1 To rsCount)

Dim rx As Double, randu As Double, gset As Double
Dim ix As Long, iy As Long, iz As Long
Dim ncyc As Long, i As Long, j As Long, a As Long
Dim u As Long

ncyc = rsCount
i = ncyc + 1
Randomize Timer
ix = Int((Rnd * 133) + 1)
iy = Int((Rnd * 2345) + 1)
iz = Int((Rnd * 1030) + 1)
rx = 0#
For j = 1 To ncyc
    x(j) = j
Next j
Do While i <> 2
        i = i - 1
        Call rando(ix, iy, iz, rx)
        u = Int((rx * i) + 1)
        a = x(i)
        x(i) = x(u)
        x(u) = a
Loop
While Not rstYourRecordSet.EOF
For j = 1 To ncyc

    rstYourRecordSet.Edit
    rstYourRecordSet("Random") = x(j)
    rstYourRecordSet.Update
    rstYourRecordSet.MoveNext
Next j
Wend

End Function


' *************************
'
      Public Function rando(ix, iy, iz, randu)

        Dim dx As Double, dy As Double, dz As Double, top As Double
        Dim c1 As Double, c2 As Double, c3 As Double
        Dim tmp As Long, l1 As Long, l2 As Long, l3 As Long
        Dim r1 As Long, r2 As Long, r3 As Long

'
'     Algorithm AS 183 Appl. Statist. (1982) vol.31, no.2
'
'         Algorithm AS183  : An efficient and portable  pseudo  random
'         number  generator.    Applied  Statistics, 31, 188-190.  op
'        cit. An efficient and portable random number  generator  :
'        Correction.  Applied  Statistics,  33,  123.   op cit. 1987,
'        May. Building  a  random  number  generator.    Byte,  pp.
'        127-128.    Onghena,  P. (1993). A theoretical and empirical
'        comparison    of  mainframe,   microcomputer,   and   pocket
'        calculator    pseudorandom  number  generators.    Behavior
'        Research Methods, Instruments, & Computers.  25, 384-395.

'    Returns a pseudo-random number rectangularly distributed
'    between 0 and 1.   The cycle length is 6.95E+12 (See page 123
'    of Applied Statistics (1984) vol.33), not as claimed in the
'    original article.
'    inputs are seed values IX,IY,IZ
'    output is pseudo random number between 0 and 1
'    which can obviously be converted to random integer
'     r=int((randu*N)+.5) etc where randu is 0,1 and N is maximum integer desired
'
'    IX, IY and IZ should be set to integer values between 1 and
'    30000 before the first entry.


         l1 = 171
         l2 = 172
         l3 = 170
         r1 = 30269
         r2 = 30307
         r3 = 30323
         c1 = CDbl(r1)
         c2 = CDbl(r2)
         c3 = CDbl(r3)
         ix = (l1 * ix) Mod r1
         iy = (l2 * iy) Mod r2
         iz = (l3 * iz) Mod r3
         dx = CDbl(ix)
         dy = CDbl(iy)
         dz = CDbl(iz)

' Generate random uniform number
          top = ((dx / c1) + (dy / c2) + (dz / c3))
          tmp = Int(top)
          randu = top - tmp
         If randu < 0 Or randu > 1 Then
            Beep
         ' Catastrophe
         ' end of world etc
         End If
      End Function

You may notice that this function does actually use the VBA function Randomize, however, as it's just to generate a seed value, all it's doing is adding another level of complexity to the randomizing function

Steven

I am a loud man with a very large hat. This means I am in charge

Bob Bedell · August 22nd, 2003, 08:17 PM

Hi Steven,

Just one more approach I was experimenting a bit with is having Rnd()
return a Long Integer instead of an Integer using the expression:

CLng(Rnd * (2 ^ 31))

This just returns your standard 4 byte Long Integer which maxes out at 2,147,483,647 (or 2 ^ 31). I used the code below to load a table with 1,000,000 records in 10 batches of 100,000 each to see if Rnd() would produce duplicate "random" sets. Not only were all the generated values apparently random (for most practical purposes I suppose); they were also unique (which is a little remarkable, given that uniqueness is obviously not a condition of randomness). You'll notice I didn't even seed Rnd() with the Randomize statement (which confuses me a bit - I assumed I'd have to given Rnd's supposed default behavior.)

I don't know what the statistical probablility of generating a duplicate value is using this expression, but it seems to be somewhere just this side of pretty astonomical. Here's the code:

Sub Test()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

For intCount = 0 To 100000

    strSQL = "INSERT INTO Table1 "
    strSQL = strSQL & "(Field1)"
    strSQL = strSQL & " VALUES ("
    strSQL = strSQL & CLng(Rnd * (2 ^ 31)) & ");"

    db.Execute strSQL

Next

    db.Close

End Sub

That said, I'm not sure this all has a particularly clear application to database work. It's generally the kind of thing that comes up in game applications where you need to role some dice, or shuffle some cards, or plot an asteroid's trajectory. I can't think of a case, off hand, where an auto-incrementing sequential number would'nt serve a database app a bit better by allowing more control over the values produced. Maybe generating passwords?

Bob

Steven · August 23rd, 2003, 05:46 AM

Bob,
I use the that randomize function in my work to randomly select people. Given the nature of my work (medical research), it's imperative that all procedures be able to stand up to scrutiny, which is why I use functions like the one above, which is well established, and has been peer reviewed.
Of course, one could always use a simple algorithm for most purposes, whether it be Rnd, or any of the many pseudo-random number generation algorithms mentioned in most number theory books

Steven

I am a loud man with a very large hat. This means I am in charge

Bob Bedell · August 23rd, 2003, 06:48 AM

Ah...sampling. How could I forget that. Used to do a little QA/Record Review myself. Been a while. Thanks Steve.

Bob