Random Number rnd()

[EBee]

For some reason the code below updates the RANDOMID field to 0's and 1's. However, at the beginning of testing I had it updating to a long number (i.e .09658526 per record.)that I can generate a list based on desired count. In this case, the top 25 from a random records using the randomID that was generated.

What 'em i doing wrong here pls.

Thank you
Access 2k
************************************

Private Sub PickRandom()
Dim db As ADODB.Connection
Dim fld As Field
Dim rst As New Recordset
Dim strSQL As String
Dim strTableName As String

Set db = CurrentProject.Connection
rst.Open "tblSelectResidence", db, adOpenKeyset, adLockOptimistic

' 3: Place a random number in the new field for each record
'Set rst = db.OpenRecordset("tblSelectResidence", dbOpenTable)
rst.MoveFirst

Do
Randomize
rst![RandomID] = Rnd()
rst.Update

rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing

' 4: Sort the data by the random number and move the top 25 into a new table
strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT TOP 25 tblSelectResidence.DistrictID, tblSelectResidence.RandomID, tblSelectResidence.AddressID, tblSelectResidence.AdrRoute, tblSelectResidence.Address, tblSelectResidence.CityID, tblSelectResidence.ZipPrefix " & _
"INTO " & strTableName & " " & _
"FROM tblSelectResidence " & _
"ORDER BY tblSelectResidence.RandomID"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 5: Delete the temporary table
' db.TableDefs.Delete ("tblTemp")
End Sub

 

[PHV]

Seems that RandomID holds only integral values ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EBee]

That was it. . I changed it to Double and it worked. now, I tried to change it from the create table query and the double does not appear to be available in the format option. How can I change it during the update of RANDOMID

Do
Randomize
rst![RandomID] = Rnd()
rst.Update

rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing

 

[PHV]

Another way is to keep RandomID as a long:
rst![RandomID] = Rnd() * 1000000

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

    [b][COLOR=red]Randomize[/color0[/b]
    
    Do
        rst![RandomID] = Rnd()
        rst.Update
        
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Set rst = Nothing

(please!)





MichaelRed