
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. 
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com



August 17th, 2003, 10:09 PM

Authorized User


Join Date: Jun 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts


Random Number
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

August 18th, 2003, 12:40 AM

Friend of Wrox


Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts


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 (CtrlG).
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 pseudorandom 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

August 21st, 2003, 11:08 PM

Friend of Wrox


Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts


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 1500) 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, 188190. 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.
' 127128. Onghena, P. (1993). A theoretical and empirical
' comparison of mainframe, microcomputer, and pocket
' calculator pseudorandom number generators. Behavior
' Research Methods, Instruments, & Computers. 25, 384395.
' Returns a pseudorandom 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

August 22nd, 2003, 08:17 PM

Friend of Wrox


Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts


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 autoincrementing sequential number would'nt serve a database app a bit better by allowing more control over the values produced. Maybe generating passwords?
Bob

August 23rd, 2003, 05:46 AM

Friend of Wrox


Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts


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 pseudorandom 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

August 23rd, 2003, 06:48 AM

Friend of Wrox


Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts


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


