[AccessD] Create a random date/time

[Stuart McLachlan]

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