Returning random ordered results in MS access

You may be shocked to hear, but back when I was young I used the now-hated Visual Basic and Access. In fact, moving these applications to PHP on the job is how I first learned PHP.

Anyways, once I was trying to return random results in an sql query to an Access MDB from VB / vba and I scoured the internet and found nothing. Then I devised this little number, which probably for all practical purposes does the trick:

ORDER BY right(right(now*10000000000, 4) ^ (1/ID),4)

where ID is the unique id for the given table. Right(now*100000000000,4) basically gets you a pseudo-random 4-digit value, the current milliseconds. We combine this with the row ID to give a different but constantly changing number for each row.

Leave a Reply

Your email address will not be published. Required fields are marked *