Using the "Random/Rnd" command....

[Dan0331]

Hi Everyone,

Is it possible, using the random option that accompanies VB6 to "shuffle" the records in a table, in Access?

If so, can you point me in the right direction?

Thanks!

[Dan0331]

I need to pull the data out of a table, mix it up, and put it back into the same table.

Any pointers, tips, suggestions?

Thanks!

[Guv]

I would not Post code without checking it, but can give you a general idea.

Define a User Type consisting of an Integer and a Single. Then Dim an array of Types.

Public Type ShuffleType 'Could be Private, I think'
   RecordNumber As Integer
   RandomNumber As Single
 End Type 

Dim ShuffleItem(1 to 500) as ShuffleType

Use Dim & ReDim if Array cannot have constant for Upper Bound.

Write a For-Loop which assigns integers 1 to Last to the integers, and which assigns a Random Number to the other variable.

Sort the array by the random number. Use the sorted array to assign new positions to the records.

I never used Access, but if it works like similar Products I have used, I suspect that you could get Access itself to do all the work. Put another field in each record to contain a Random Number. Assign Random Numbers to the new Field. Tell Access to sort by the field.