Random number using Date() in Expression Builder

I want to generate random number using Date() to format it like for example: ddmmyyyyHhNnSs

How do I achieve that? Is that even possible?
I was kinda hoping I can do it easy way by the expression builder but I seem to fail on each approach ;)

This number need to populate txtID field and it will be unique identifier for each database entry.

回答1:

That's quite easy - with a twist.

Problem is that Rnd only returns a Single and the resolution of this only allows for 10000000 unique values. As you request a resolution to the second and with 86400 seconds per day, that only leaves a span of 115.74 days while the range of Date spans 3615899 days:

TotalDays = -CLng(#1/1/100#) + CLng(#12/31/9999#)

To overcome this, use Rnd twice which will result in 1E+15 possible values or 11574074074 days - way beyond what's needed:

RandomDouble = Rnd * Rnd

Now, to limit the possible values to fit into the range of data type Date, just follow the documentation:

RandomValue = (UpperValue - LowerValue) * Rnd + LowerValue

and apply the date values:

RandomDouble = (CLng(#12/31/9999#) - CLng(#1/1/100#)) * Rnd * Rnd + CLng(#1/1/100#)

This, however, will result in values containing unwanted milliseconds, thus perform the proper conversion to Date value using CDate which will round to the nearest second, and you have the final expression:

RandomDate = CDate((CLng(#12/31/9999#) - CLng(#1/1/100#)) * Rnd * Rnd + CLng(#1/1/100#))

Use the value as is if your field is of datatype Date or - if text - apply a format to this with Format(RandomDate, "yyyymmddhhnnss") and a sample output will be:

01770317032120
01390126010945
50140322081227
35290813165627
09330527072433
20560513105943
61810505124235
09381019130230
17010527033132
08310306233911

If you want numeric values, use CDec to convert (CLng will fail because of overflow):

RandomNumber = CDec(Format(RandomDate, "yyyymmddhhnnss"))

All said, I'm with @Bohemian - if you just want a unique timestamp and have less than one transaction per second, just use data type Date for your field and use Now:

TimeStamp = Now()

and apply a format to this of yyyymmddhhnnss.

However, Multiplying random numbers together alters the probablility distribution:

Uniform Product Distribution

Thus, a better method is to create a random date, then a random time, and possibly a random count of milliseconds - I wrote above, that CDate rounds a value to the nearest second; it doesn't, only whenever Access displays a date/time with milliseconds the displayed valued is rounded to the second.

So I modified the function to take care of this:

Public Function DateRandom( _
    Optional ByVal UpperDate As Date = #12/31/9999#, _
    Optional ByVal LowerDate As Date = #1/1/100#, _
    Optional ByVal DatePart As Boolean = True, _
    Optional ByVal TimePart As Boolean = True, _
    Optional ByVal MilliSecondPart As Boolean = False) _
    As Date

'   Generates a random date/time - optionally within the range of LowerDate and/or UpperDate.
'   Optionally, return value can be set to include date and/or time and/or milliseconds.
'
'   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.
'   2015-08-30. Modified to return selectable and rounded value parts for
'               Date, Time, and Milliseconds.
'   2015-08-31. An initial  call of Randomize it included to prevent identical sequences.

    Const SecondsPerDay As Long = 60& * 60& * 24&

    Dim DateValue   As Date
    Dim TimeValue   As Date
    Dim MSecValue   As Date

    ' Shuffle the start position of the sequence of Rnd.
    Randomize

    ' If all parts are deselected, select date and time.
    If Not DatePart And Not TimePart And Not MilliSecondPart = True Then
        DatePart = True
        TimePart = True
    End If
    If DatePart = True Then
        ' Remove time parts from UpperDate and LowerDate as well from the result value.
        ' Add 1 to include LowerDate as a possible return value.
        DateValue = CDate(Int((Int(UpperDate) - Int(LowerDate) + 1) * Rnd) + Int(LowerDate))
    End If
    If TimePart = True Then
        ' Calculate a time value rounded to the second.
        TimeValue = CDate(Int(SecondsPerDay * Rnd) / SecondsPerDay)
    End If
    If MilliSecondPart = True Then
        ' Calculate a millisecond value rounded to the millisecond.
        MSecValue = CDate(Int(1000 * Rnd) / 1000 / SecondsPerDay)
    End If

    DateRandom = DateValue + TimeValue + MSecValue

End Function

回答2:

Format now() and cast to a long:

select CLng(format(now(), 'ddmmyyyyhhnnss')) as txnId

Although this is not "random", it is unique as long as there are never more than one transaction per second (confirmed in comment above).