Random Date Generation

[CutAndPaste]

I often need to generate random numbers within a given range to populate a table with test info. I've managed this fine with numbers in a function using something like:

Me.Field1 = Int((8 * Rnd()) + 1)'Generates random value between 1 and 8

However I can't seem to do this with dates. I need to specifiy and end and start date and generate a random date between these values. how do I do this?

Thanks

 

[KDg]

Hi CaP,

the below should work

Function RandDate(dtmStartDate As Date, dtmEndDate As Date) As Date
    Randomize
    RandDate = DateAdd("d", Int((DateDiff("d", dtmStartDate, dtmEndDate) * Rnd()) + 1), dtmStartDate)
End Function

Sub ghghg()
    MsgBox RandDate(#1/13/01#, #1/21/01#)
End Sub

the RandDate= line looks abit like overkill, using 2 date functions when you could just subtract and add them, but it's safer to use the functions. You never know when M$ might go and change something on you...

HTH

Drew

 

[CutAndPaste]

Thanks,

I can follow the first part of the function but I'm not sure where the 2nd sub comes in?

can you explain it a little more?
Do I need to call both functions? When?
Thanks!

It may be an inelegant way of populating these records with random values but I'm calling my random number generator function on the lost focus event of one of the fields then just whizzing through all the records with the nav buttons. Gets it done..

 

[KDg]

You don't need the second at all, that was just me checking that it ran okay - didn't actually mean to post it...Drew

 

[CutAndPaste]

KdG,

Thanks for the reply, though I'm still scratching my head as to where I enter the [StartDate] and [EndDate] to limit the range of the random dates. In most cases when I want to run this function the dates would be fixed so can be hard coded rather than entered from a form etc.

From which Action would I call this function? If I run it from the OnLostFocus of my field as mentioned above where to I reference the required field to be updated?

Thanks...

 

[KDg]

Oh i get ya. I thought you were taking the p out of my ghghg 'routine'! oops

.
You can get data into it in various ways - including hard coding as i did above, or if you do want to use boxes on a form then
RandDate(Me.StartDateField,Me.EndDateField)

If you want to put the value on a form then you'd call it as -

Me.[i]NameOfFieldYouWantRandomDateIn[/i]=RandDate(Me.[i]StartDateField[/i],Me.[i]EndDateField[/i])

Wich is probably best for the way you're doing it. You could create 2 form level variables ( eg dtmStartDate as Date and dtmEndDate as Date ) and set them when the form opens

eg dtmStartDate=InputBox("When do u wann start from?")
dtmEndDate=InputBox("When do u wanna stop?"


If that doesn't help, please keep posting until u get what u need!

Cheers

Drew

 

[CutAndPaste]

Drew,

Worked like a dream, I was giggling away with glee as this function created lots of info for my test data tables! Saved me loads of typing!!!!!

Thanks,

Simon