Trying to populate field with random generated date

  • Thread starter Thread starter Dale C Gray
  • Start date Start date
D

Dale C Gray

So I've built this function and it works well enough?...but I want a date
range for the year between 1930 and 2003. I can't seem to get anything
greater than 1924. Part of the problem is I'm not clear on what I should be
using as my upper and lower date limits or I guess even how this formula
calculates! Any suggestions...comments....
Thanks all

Function GenDate() As Date
Dim db As DATABASE
Dim rst As Recordset
Dim lngcounter As Long
Dim dteRandom As Date
Dim yupper As Long, mupper As Long, dupper As Long
Dim ylower As Long, mlower As Long, dlower As Long
Dim yRandom As Long, mRandom As Long, dRandom As Long

Dim strCriteria As String
Set db = CurrentDb
Set rst = db.OpenRecordset("tblPatient", dbOpenDynaset)

Do Until rst.NoMatch
yupper = 2003
ylower = 1930
mupper = 12
mlower = 1
dupper = 30
dlower = 1
Restart:
Randomize
yRandom = ((Int((yupper - ylower)) * Rnd) + ylower)
Randomize
mRandom = Int(12 * Rnd)
Randomize
dRandom = Int(30 * Rnd)


lngRandom = CDate(yRandom + mRandom + dRandom)

rst.FindFirst "[DOB] = #" & lngRandom & "#"
If rst.NoMatch Then

GenDate = dteRandom
' Else: GoTo Restart:

End If


Loop

' Clean up.
rst.Close
Set db = Nothing


End Function
 
Dates are stored in Access as floating point numbers, where the integer part
represents the date, and the fraction represents the time of day.

By converting the date to a long integer, you can work with it numerically,
and calculate a random number of days since 1930:

DateAdd("d", CLng((CLng(Date) - CLng(#1/1/1930#)) * Rnd), #1/1/1930#)
 
Allen Browne said:
Dates are stored in Access as floating point numbers, where the
integer part represents the date, and the fraction represents the
time of day.

By converting the date to a long integer, you can work with it
numerically, and calculate a random number of days since 1930:

DateAdd("d", CLng((CLng(Date) - CLng(#1/1/1930#)) * Rnd), #1/1/1930#)


Dale C Gray said:
So I've built this function and it works well enough?...but I want a
date range for the year between 1930 and 2003. I can't seem to get
anything greater than 1924. Part of the problem is I'm not clear on
what I should be using as my upper and lower date limits or I guess
even how this formula calculates! Any suggestions...comments....
Thanks all

Function GenDate() As Date
Dim db As DATABASE
Dim rst As Recordset
Dim lngcounter As Long
Dim dteRandom As Date
Dim yupper As Long, mupper As Long, dupper As Long
Dim ylower As Long, mlower As Long, dlower As Long
Dim yRandom As Long, mRandom As Long, dRandom As Long

Dim strCriteria As String
Set db = CurrentDb
Set rst = db.OpenRecordset("tblPatient", dbOpenDynaset)

Do Until rst.NoMatch
yupper = 2003
ylower = 1930
mupper = 12
mlower = 1
dupper = 30
dlower = 1
Restart:
Randomize
yRandom = ((Int((yupper - ylower)) * Rnd) + ylower)
Randomize
mRandom = Int(12 * Rnd)
Randomize
dRandom = Int(30 * Rnd)


lngRandom = CDate(yRandom + mRandom + dRandom)

rst.FindFirst "[DOB] = #" & lngRandom & "#"
If rst.NoMatch Then

GenDate = dteRandom
' Else: GoTo Restart:

End If


Loop

' Clean up.
rst.Close
Set db = Nothing


End Function
Click to expand...
Click to expand...

Incidentally, Dale, I don't think you need to call Randomize more than
once. That call is supposed to reseed the random number generator, but
you don't need to do that before every call to Rnd().
 
Thanks Allen and Dirk...awesome stuff you gentlemen do...thank you for all
your help past, present and future.

Thanks Allen..I would never have dreamed up such a formula!!


Dirk Goldgar said:
Allen Browne said:
Dates are stored in Access as floating point numbers, where the
integer part represents the date, and the fraction represents the
time of day.

By converting the date to a long integer, you can work with it
numerically, and calculate a random number of days since 1930:

DateAdd("d", CLng((CLng(Date) - CLng(#1/1/1930#)) * Rnd), #1/1/1930#)


Dale C Gray said:
So I've built this function and it works well enough?...but I want a
date range for the year between 1930 and 2003. I can't seem to get
anything greater than 1924. Part of the problem is I'm not clear on
what I should be using as my upper and lower date limits or I guess
even how this formula calculates! Any suggestions...comments....
Thanks all

Function GenDate() As Date
Dim db As DATABASE
Dim rst As Recordset
Dim lngcounter As Long
Dim dteRandom As Date
Dim yupper As Long, mupper As Long, dupper As Long
Dim ylower As Long, mlower As Long, dlower As Long
Dim yRandom As Long, mRandom As Long, dRandom As Long

Dim strCriteria As String
Set db = CurrentDb
Set rst = db.OpenRecordset("tblPatient", dbOpenDynaset)

Do Until rst.NoMatch
yupper = 2003
ylower = 1930
mupper = 12
mlower = 1
dupper = 30
dlower = 1
Restart:
Randomize
yRandom = ((Int((yupper - ylower)) * Rnd) + ylower)
Randomize
mRandom = Int(12 * Rnd)
Randomize
dRandom = Int(30 * Rnd)


lngRandom = CDate(yRandom + mRandom + dRandom)

rst.FindFirst "[DOB] = #" & lngRandom & "#"
If rst.NoMatch Then

GenDate = dteRandom
' Else: GoTo Restart:

End If


Loop

' Clean up.
rst.Close
Set db = Nothing


End Function
Click to expand...
Click to expand...

Incidentally, Dale, I don't think you need to call Randomize more than
once. That call is supposed to reseed the random number generator, but
you don't need to do that before every call to Rnd().

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top