[AccessD] SPAM-LOW: Re: Random ina query

jwcolby jwcolby at colbyconsulting.com
Fri May 15 11:57:24 CDT 2009


couldn't you do something like: (air code)

function Rndm()
static blnInitialized as boolean
	if blnInitialized then
		rndm = Random()
	else
		Randomize
		Rndm = Random()
		blnInitialized = true
	endif
end function

Then the randomize would occur in the same scope as the call to random().

Or does the fact that the function is called in a query somehow prevent the static from sticking 
around?  Or some other issue?

John W. Colby
www.ColbyConsulting.com


Gustav Brock wrote:
> Hi Jim
> 
> No. As I wrote to Susan (note the word "initially"):
> 
> <quote>
> I had to deal with this and found out that the common method spread around the web doesn't work.
> 
> The problem is that calling Randomize outside the query (by an external function) takes place in a different scope than that of the query, thus Rnd(..) is run in the query with the same seed initially which, of course, will return the same sample or sequence of samples.
> </quote>
> 
> The years passed by and Susan forgot all about it, so in 2007 she wrote a tip without mentioning this trap:
> 
>   http://blogs.techrepublic.com.com/howdoi/?p=149
> 
> She does discuss a UDF but, unfortunately, that is for another purpose.
> 
> /gustav
> 
> 
>>>> jimdettman at verizon.net 15-05-2009 17:42:19 >>>
> 
>   I forgot to add a field to the call, but otherwise, that will work. With
> it defined as I posted, it would get called once at the start of the query.
> It needs to be Rnd([<fieldname>]).   If you don't have a non-zero numeric
> field, you can do Rnd(Len([<field name>])
> 
>   But other then that, it does work.
> 
> Jim.
> 
>   
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Friday, May 15, 2009 11:25 AM
> To: accessd at databaseadvisors.com 
> Subject: Re: [AccessD] Random ina query
> 
> Hi Jim
> 
> Not you! I've posted that code many times here and 14 times at [you know
> where] since 2004.
> 
> I even found my function here (with left out credits of course):
> 
>   http://www.vbforums.com/showthread.php?t=388276 
> 
> /gustav
> 
> 
>>>> jimdettman at verizon.net 15-05-2009 17:07:48 >>>
> 
>   Yup.  Define a column as Rnd(1), sort on that column and set the TOP
> predicate for the query for the number of records you want.
> 
> 
> 



More information about the AccessD mailing list