[AccessD] Create a random date/time

[Bill Benson]

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
>