random...

[Lafor]

Guys/Gals
Any idea as to how to randomly select 10 records from a table
of users? using sql

[peet]

not sure if I can give u an answer using SQL only... is opening a recset and pick them from there ok?

[Lafor]

Would be fine... but How to randomize.. it

[peet]

I'll post a sample for u in a couple of hours... sorry about the delay.

[peet]

hmm..funny stuff

Private Sub Command1_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim iNumOfUsersToPick
    Dim iUsed() As Integer
    Dim bUsed As Boolean
    Dim iNextUser As Integer
    Dim i As Integer
    Dim x As Integer
    Dim sUserList As String
    Dim iSelMax As Integer
    
    Randomize Timer
    
    iNumOfUsersToPick = 10
    ReDim iUsed(iNumOfUsersToPick)
    
    Set db = OpenDatabase("D:\TEST.MDB")
    Set rs = db.OpenRecordset("Users")
    rs.MoveLast
    rs.MoveFirst
    iSelMax = rs.RecordCount
    For i = 0 To iNumOfUsersToPick
        bUsed = True
        While bUsed
            bUsed = False
            iNextUser = Int(iSelMax * Rnd)
            For x = 0 To iNumOfUsersToPick
                If iNextUser = iUsed(x) Then
                    bUsed = True
                    Exit For
                End If
            Next x
        Wend
        iUsed(i) = iNextUser
        rs.MoveFirst
        rs.Move iNextUser
        sUserList = sUserList & rs("Name") & vbCrLf
    Next i
    MsgBox sUserList
End Sub

[peet]

Database : D:\TEST.MDB
Table: Users
Field: Name



if u have any questions regarding the above sample, just ask