[AccessD] Create a random date/time

[Bill Benson]

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
>