Dear Bill Wilkinson

Results 1 to 2 of 2

Thread: Dear Bill Wilkinson

  1. #1
    moti Guest

    Default Dear Bill Wilkinson

    Dear Bill I have posted before but others tryed to take your place ,And i could not leave it .<BR>,<BR>Thanks for the random db function you have helped me last week and explain ,<BR>One problem that in the autonumber when their is a delete the next number comes out after the delete , so let&#039s say i have 45 records but the numbers may be from 1 to 70 with numbers missing so the random number only using the count does not help me !<BR>Yesterday MG has gave me in the forum a article of 4guys about this issue but the function has allkind of new operators and pbjects i dont know , He was very kind but i am stuck ,<BR>I know your time is important but please help me if possible .<BR>Thanks <BR>Moti

  2. #2
    Join Date
    Dec 1969

    Default Random records without autonumber...

    If you really have only 45 records to choose fact, if you have no more than 200 or so records to choose from...then here&#039s another way:<BR><BR>&#060;%<BR>... you set up the connection ...<BR><BR>&#039 you make the query, but be sure to list the fields explicitly, like this:<BR>Set RS = conn.Execute("SELECT field1, field2, field3 FROM table")<BR><BR>allRows = RS.GetRows<BR>rowCount = UBound( allRows, 2 ) + 1<BR><BR>Dim randomRows( 9 ) &#039 one less than number of random numbers wanted<BR>foundCount = -1 &#039 keeps track of number of random numbers found<BR><BR>Do While foundCount &#060; UBound( randomRows )<BR>&nbsp;&nbsp;&nbsp;&nbsp;recNum = Int( Rnd * rowCount )<BR>&nbsp;&nbsp;&nbsp;&nbsp;found = False<BR>&nbsp;&nbsp;&nbsp;&nbsp;For i = 0 To foundCount<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;If randomRows(i) = recNum Then found = True<BR>&nbsp;&nbsp;&nbsp;&nbsp;Next<BR>&nbsp;&nbs p;&nbsp;&nbsp;If found = False Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;foundCount = foundCount + 1<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;randomRows( foundCount ) = recNum<BR>&nbsp;&nbsp;&nbsp;&nbsp;End If<BR>Loop<BR>%&#062;<BR><BR>And now the array "randomRows" contains 10 (or however big you made it) random numbers that refer to rows in the "allRows" array of records!<BR><BR>So then you can *use* the random records, thus:<BR><BR>&#060;%<BR>For n = 0 To UBound( allRows, 2 )<BR>&nbsp;&nbsp;&nbsp;&nbsp;rownum = randomRows(n)<BR>&nbsp;&nbsp;&nbsp;&nbsp;Response. Write "&#060;TD&#062;" & allRows(0,rownum) & "&#060;/TD&#062;&#060;TD&#062;" & allRows(1,rownum) ... etc. ...<BR>&nbsp;&nbsp;&nbsp;&nbsp;...<BR>Next<BR>%&#0 62;<BR><BR>The "trick" here is that the array from GetRows ("allRows" in the example) is indexed via<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;fieldValue = allRows( fieldNumber, recordNumber )<BR>which is why it is important to explicitly call out the field names when you do the "SELECT ...", so that you *know* the order of the fields in the array.<BR><BR>Okay?<BR><BR>NOTES: <BR>(1) You do *not* need an autonumber field for this to work. You do not need to include the autonumber field in the list of SELECT fields unless you need it for some other reason.<BR><BR>(2) This is probably not a good thing to do if the total number of records you will select from exceeds 200 or so. (Well, actually, it is the product of number of records time number of fields that matters make that product be no more than, say 2000? Preferably 1000?)<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts