How to generate random numbers.
Q: Bob wants to know if there is some way to generate random numbers within a certain range (he needs to fill a field in a table with sample values).
A: Yes, there is an Rnd function you can use for this purpose. It returns a Single value, so you may need to convert its return value to the data type you want to use. Here is an example that will create an Integer value between 10 and 100
Int((100 – 10) * Rnd + 9)
To create a random date within a certain range, use code like this:
strDay = CStr(Int((28 – 1) * Rnd + 1))
strMonth = CStr(Int((12 – 1) * Rnd + 1))
strYear = CStr(Int((2008 – 1900) * Rnd + 1901))
dteTest = CDate(strDay & “/” & strMonth & “/” & strYear)
To write a random value to a certain field in every record of a table, use code like the following:
Public Function FillAges()
On Error GoTo ErrorHandler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblMembers”)
Do While Not rst.EOF
rst.Edit
rst![SignupAge] = Int((120 – 18) * Rnd + 17)
rst.Update
rst.MoveNext
Loop
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End Function
For filling a field with random dates, use the following code:
Public Function FillDates()
On Error GoTo ErrorHandler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strMonth As String
Dim strDay As String
Dim strYear As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblMembers”)
Do While Not rst.EOF
rst.Edit
strDay = CStr(Int((28 – 1) * Rnd + 1))
strMonth = CStr(Int((12 – 1) * Rnd + 1))
strYear = CStr(Int((2008 – 1900) * Rnd + 1901))
rst![BirthDate] = CDate(strDay & “/” & strMonth & “/” & strYear)
rst.Update
rst.MoveNext
Loop
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End Function
