... Well ... here's the deal ... the Rnd() function will return predictable results with the solution Khawar has proposed.
Upon opening the db you will get, without fail the following sequence ...
1st run ...
Buchanan, Steven $22.98
Buchanan, Steven $890.78
2nd run ...
Buchanan, Steven $13.02
Buchanan, Steven $19.58
3rd run ...
Buchanan, Steven $109.11
Buchanan, Steven $163.97
.... The pattern is COMPLETELY predicitable simply because the Rnd() function has NOT been seeded using the Randomize statement, so you get the same series of numbers returned from Rnd().
Upon database open, without being seeded with Randomize ... Rnd() returns the following sequence (A2003) ...
The list could go on, but I think you see the point ... so ... that is why the Randomizer fuction was used in the code from the initial post, to seed Rnd(). You only have to call the Randomize statement once, and to me there is really very little if any gain by wrapping it in a function call like Randomizer(). I will typically just execute the Randomize right before the Rnd() call
... So ... if you like Khawars technique (which is a good technique for this sort of task!), I would suggest you change the field datatype of Rndm to Number/Single, then change to GetRandom() function to something like this ...
Code:
Public Function GetRandom(Optional lngValue As Long) As Single
Randomize
GetRandom = Rnd()
End Function
If you don't like the idea of executing Randomize on each call to GetRandom(), then do something like this ...
Code:
Public Function GetRandom(Optional lngValue As Long) As Single
Static btIsSeeded As Byte
If btIsSeeded = 0 Then
Randomize
btIsSeeded = 1
End If
GetRandom = Rnd()
End Function
Note that I made it so the function accepts an optional argument. This is simply to ensure muliple calls from a Query Object. If a function call does not change from record to record, Access/JET will only call the function once and cascade that value throughout the records returned, therfore I have the optional argument to provide for that change from record to record. The argument is unused in the code simply because its not needed to produce a random number. So, when calling the function from VBA, there is no need to supply the argument.
One more side note concerning Rnd() ...
Rnd() returns a Single, and as such has 7 digits to the right of the decimal place, and of those, I am pretty sure the last one is not as random as it could be simply because of the conversion from binary to a 4byte floating point number.... so if you see someone using Rnd() to set a Double typed variable ... the value is not really random past the 7th digit!
I hate to hijack a thread but I have a similar situation and I don't know where to begin. I hope some of you are still out there to help!
Basically, I have a table with about 6 different fields, UserID, Date, transaction, etc. I need to generate a query that will provide me 5 random transactions of a UserID. So Iif there are 10 transactions of "Jim", I need 5 and then go on to the next UserID and so forth. I feel like I'm very close with all the help, but I feel like I'm so far away. Anyhelp would be much appreciated!
I think I'm getting close. I have the following so far using khawar's previous post. As I've stated it's one table and I just need random 5 of each UserID (text). ID below is an issue because ID is a primary key that is generated by access but I don't necessary want to be sorted by ID, rather UserID but I can't insert it to multiply by the random number generator since it's a text column.
Right now, the query doesn't give me an error but it also doesn't give me what I want. Any help would be much appreciated. Thank you!!
SELECT [PPA Data].*
FROM [PPA Data]
WHERE ((([PPA Data].UserID) In (SELECT Top 3 [UserID] FROM [PPA Data]
WHERE [UserID]=[PPA Data].[UserID]
Order By
Getrandom()*([ID]) Desc)));
Option Compare Database
Dim RandNum As Long
Function GetRandom()
If RandNum > 100 Then
RandNum = 0
Else
RandNum = RandNum + (Int((100 - 1 + 1) * Rnd + 1))
End If
GetRandom = RandNum
End Function
