[AccessD] Create a random date/time

[Gustav Brock]

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