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

Stuart McLachlan stuart at lexacorp.com.pg
Fri May 15 18:07:18 CDT 2009


You don't need to go to all that trouble. You can do it with a one line function:

Function Rndm(FieldVal as Variant) as Single
   Rndm = Rnd()
End Function.

 - as long as you call Randomize at some stage before opening the query.

You can do so in a Startup() function invoked from an Autoexec macro, in the On_open of 
your startup form or in an event procedure which opens the query.


-- 
Stuart

On 15 May 2009 at 12:57, jwcolby wrote:

> 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.
> > 
> > 
> > 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com





More information about the AccessD mailing list