Query for a random record 1

[dmh4ab]

I'm new to VBA and still a beginner in SQL. I need to be able to have Access (97) to generate a random number and pull the record associated with that record number from a query. I know I can count the records in the query using Count. And I know VBA has the capability to generate a random number. I'm just lost as to how to put the two together.

Any help would be greatly appreciated.

Thanks,
dmh4ab

 

[JoyInOK]

Create a recordset of the records you want to use.
dim rstMine as DAO.Recordset
dim strSQL as string
strSQL = "SELECT fldRecordNo FROM tblMyTable;" 'replace with your SQL statement
set rstMine = currentDB.OpenRecordset(strSQL)
rstMine.move last 'Force Access to get an accurate recordcount
dim intLast as integer
intLast = rstMine.recordcount
intFirst = 1 'the first record
dim intMyRandomRecordNumber as integer

intMyRandomRecordNumber = Int((intLast- intFirst+ 1) * Rnd + intFirst)
'release the variable
set rstMine = nothing

 

[oharab]

If all you want to do is pull out a record at random then add this code to a module:

'Code courtesy of
'Joe Foster
'************ Code Begin ***********
Function Randomizer () As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize : AlreadyDone = True
Randomizer = 0
End Function
'************ Code End *************

Now to get a question picked at random:

select top 1 mytable.*from mytable
where randomizer() = 0
order by rnd(isnull(mytable.question) * 0 + 1)

hth

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[dmh4ab]

oharahb,

Thanks! Your code and query (combined with a little more digging I had done myself) was the perfect solution!

Thank you very much!

dmh4ab