[AccessD] Create a random date/time

Stuart McLachlan stuart at lexacorp.com.pg
Sat Aug 29 19:13:13 CDT 2015 

Not really a flaw, you are coming up against a rounding error. 

Since there are only 86,400 seconds in a day, In 1,000,000 random numbers you are going 
to get a few which round up to 1 when converted  to a DateTime and displayed to the nearest 
second (the default).   On average, you will get the next day once in every 172,800 iterations 
if you just want times in a single date.   That number is multiplied by the number of days in 
your range, so if you want a random date within 365 days, it will only happen once on 
average in 63 million iterations.

Do you still get the same problem if you use  ? Format(dtMax,"d mmm yyy hh:nn:ss:ms")

-- 
Stuart


On 29 Aug 2015 at 19:42, Bill Benson wrote:

> I disagree.
> 
> If I ask for a random date between today and today you had better not
> give me tomorrow.
> 
> Confused how this can even be confusing!
> On Aug 29, 2015 7:41 PM, "Stuart McLachlan" <stuart at lexacorp.com.pg>
> wrote:
> 
> > No, it's doing what you are asking it to since you are using the
> > same dt=Date for the bounds.
> >
> > Try this:
> >
> >  Sub testformula()
> >  Dim dt As Date
> >  Dim i As Long
> >  Dim dttemp As Date
> >  Dim DtHi As Date
> >  Dim dtLo As Date
> >  DtHi = CDate("1/1/2015")
> >  dtLo = CDate("31/12/2015")
> >  Dim dtMax As Date
> >  Dim dtMin As Date
> >  dtMin = CDate("1/1/2100")
> >  dtMax = CDate("1/1/1900")
> >  For i = 1 To 1000000#
> >      dttemp = CDate((CLng(DtHi) - CLng(dtLo)) * Rnd + CLng(dtLo) +
> >      Rnd) If dttemp > dtMax Then dtMax = dttemp If dttemp < dtMin
> >      Then dtMin = dttemp
> >  Next
> >  Debug.Print dtMin, dtMax
> >  End Sub
> >
> >
> >
> > On 29 Aug 2015 at 14:55, Bill Benson wrote:
> >
> > > There is a flaw in that function. Consider this for the current
> > > date
> > >
> > > Sub testformula()
> > > Dim dt As Date
> > > Dim i As Long
> > > Dim dttemp As Date
> > > dt = Date
> > > Dim dtMax As Date
> > > Dim dtMin As Date
> > > dtMin = CDate("1/1/2100")
> > > dtMax = CDate("1/1/1900")
> > > For i = 1 To 1000000#
> > >     dttemp = CDate((CLng(dt) - CLng(dt)) * Rnd + CLng(dt) + Rnd)
> > >     If dttemp > dtMax Then dtMax = dttemp If dttemp < dtMin Then
> > >     dtMin = dttemp
> > > Next
> > > Debug.Print dtMin, dtMax
> > > End Sub
> > >
> > > I got an answer of
> > >
> > > 8/29/2015     8/30/2015
> > >
> > >
> > > On Sat, Aug 29, 2015 at 2:10 PM, Gustav Brock <gustav at cactus.dk>
> > > wrote:
> > >
> > > > Hi Stuart
> > > >
> > > > Thanks for the link - you are absolutely right - why didn't I
> > > > think about this? While I believe - for the purpose - one could
> > > > live with this limitation, the modification needed is really
> > > > really simple:
> > > >
> > > > Initial method:
> > > >
> > > >     RandomDate = CDate((CLng(UpperDate) - CLng(LowerDate)) * Rnd
> > > >     * Rnd +
> > > > CLng(LowerDate))
> > > >
> > > > Modified as to your suggestion:
> > > >
> > > >     RandomDate = CDate((CLng(UpperDate) - CLng(LowerDate)) * Rnd
> > > >     + Rnd +
> > > > CLng(LowerDate))
> > > >
> > > > This is possible because Rnd returns values >= 0 and < 1,
> > > > exactly matching the numeric value range of Time.
> > > >
> > > > The corrected function:
> > > >
> > > > <code>
> > > > Public Function DateRandom( _
> > > >     Optional ByVal UpperDate As Date = #12/31/9999#, _
> > > >     Optional ByVal LowerDate As Date = #1/1/100#) _
> > > >     As Date
> > > >
> > > > '   Generates a random date/time - optionally within the range
> > > > of LowerDate and/or UpperDate. ' '   2015-08-28. Gustav Brock,
> > > > Cactus Data ApS, CPH. '   2015-08-29. Modified for uniform
> > > > distribution as suggested by Stuart McLachlan by '              
> > > > combining a random date and a random time.
> > > >
> > > >     Dim RandomDate  As Date
> > > >
> > > >     ' Random date: (CLng(UpperDate) - CLng(LowerDate)) * Rnd +
> > > > CLng(LowerDate)
> > > >     ' Random time: Rnd
> > > >     RandomDate = CDate((CLng(UpperDate) - CLng(LowerDate)) * Rnd
> > > >     +
> > > > CLng(LowerDate) + Rnd)
> > > >
> > > >     DateRandom = RandomDate
> > > >
> > > > End Function
> > > > </code>
> > > >
> > > > /gustav
> > > >
> > > > ________________________________________
> > > > Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af
> > > > Stuart McLachlan <stuart at lexacorp.com.pg> Sendt: 29. august 2015
> > > > 03:21 Til: Access Developers discussion and problem solving
> > > > Emne: Re: [AccessD] Create a random date/time
> > > >
> > > > Rnd * Rnd is NOT a "trick to create a random date/time".
> > > >
> > > > It is a trick to create "more significant digits" than the
> > > > Single returned by a Rnd.
> > > >
> > > > Unfortunately it has a VERY  bad side effect. Multiplying random
> > > > numbers together alters the probablility distribution.
> > > > http://mathworld.wolfram.com/UniformProductDistribution.html
> > > >
> > > > A better way would be to use Rnd twice in a different way. First
> > > > to generate the Date part and a second time to generate the time
> > > > part, then add then together.
> > > >
> > > > --
> > > > Stuart
> > > >
> > > > On 28 Aug 2015 at 15:29, Gustav Brock wrote:
> > > >
> > > > > Hi all
> > > > >
> > > > > Have you ever wondered how to create a random date/time?
> > > > >
> > > > > Well I hadn't, but it is not that difficult. The trick is
> > > > > using Rnd twice:
> > > > >
> > > > >     RandomDate = CDate((CLng(#12/31/9999#) - CLng(#1/1/100#))
> > > > >     * Rnd * Rnd + CLng(#1/1/100#))
> > > > >
> > > > > The full story is here:
> > > > >
> > > > >     http://stackoverflow.com/a/32265346/3527297
> > > > >
> > > > > If you want a value within a given range, you can use this
> > > > > simple function:
> > > > >
> > > > > <code>
> > > > > Public Function DateRandom( _
> > > > >     Optional ByVal UpperDate As Date = #12/31/9999#, _
> > > > >     Optional ByVal LowerDate As Date = #1/1/100#) _
> > > > >     As Date
> > > > >
> > > > > '   Generates a random date/time - optionally within the range
> > > > > of LowerDate and/or UpperDate. ' '   2015-08-28. Gustav Brock,
> > > > > Cactus Data ApS, CPH.
> > > > >
> > > > >     Dim RandomDate  As Date
> > > > >
> > > > >     RandomDate = CDate((CLng(UpperDate) - CLng(LowerDate)) *
> > > > >     Rnd * Rnd
> > > > >     + CLng(LowerDate))
> > > > >
> > > > >     DateRandom = RandomDate
> > > > >
> > > > > End Function
> > > > > </code>
> > > > >
> > > > > /gustav
> > > > --
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> -- 
> 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