Random time

[joshirohany]

Dear Experts,
I am trying to generate Random time between two time range format hh:mm:ss. i am able to do in excel can you guide how it can be done in Ms Access.


Thanks and your help is appreciated.

 

[theDBguy]

Hi. You should be able to use the Rnd() function for that. What have you tried?

 

[joshirohany]

I am able to generate Random in number(integer) but not in date. i am using

Int ((200 - 150 + 1) * Rnd + 150)

 

[theDBguy]

I am able to generate Random in number(integer) but not in date. i am using

Int ((200 - 150 + 1) * Rnd + 150)

Okay, that's good. Now, you just need to convert it to a date. Try using the DateAdd() function for that.

 

[The_Doc_Man]

Can you tell us just a little bit more of what you wanted to do? Obviously you are looking to create a random time for some purpose, but the units might be confusing to you if you don't understand them. So ... are we talking a random number of days? A random number of hours? Something else? What are you trying to accomplish?

 

[MajP]

Public Function MyRandomTime(ByVal StartRange As Date, ByVal EndRange As Date, Optional Seed As Variant = 1) As Date
  'You have to seed it with a unique value per record if used in a query
  'To get a random long between two integers/longs
  Randomize
   Dim Range As Date
    Range = EndRange - StartRange
  MyRandomTime = CDate(CDbl(StartRange) + CDbl(Range) * Rnd(Seed))
End Function

Public Sub RandomTime()
  Dim I As Integer
  For I = 1 To 10
   Debug.Print MyRandomTime(#3/5/2020 10:00:00 AM#, #3/5/2020 10:30:00 AM#, 1)
  Next I
End Sub

Results

3/5/2020 10:28:53 AM
3/5/2020 10:05:37 AM
3/5/2020 10:07:27 AM
3/5/2020 10:15:59 AM
3/5/2020 10:22:45 AM
3/5/2020 10:23:36 AM
3/5/2020 10:29:02 AM
3/5/2020 10:04:44 AM
3/5/2020 10:01:44 AM
3/5/2020 10:18:43 AM

 

[joshirohany]

Firstly thanks to all of you.

All i am trying, is to generate In/Out time for employees. I have attached the excel sheet that i am currently using. Need the same in Access.

 

[MajP]

And the answer previously provided?

 

[ebs17]

I am able to generate Random in number(integer)

You can convert a time to seconds (=> long):

?DateDiff("s", #0:00:00#, #11:02:15#)
 39735

That would be done for the area boundaries. The random number that can now be determined can be calculated back in time:

?DateAdd("s", 39999, #0:00:00#)
11:06:39

 

[joshirohany]

Hello MajP,
I downloaded your demo. Its showing below details.

 

[MajP]

Not the right file. Not sure what that is. Standby.

 

[MajP]

Try this file instead

 

[joshirohany]

Its exactly what is required. Thanks sir and all once again.

 

[MajP]

Fyi,
I noticed that it does not include end time values. So in the AM you get values equally distributed from 7:45 to 7:59, but not 8:00. You can modify the code or just extend the ranges by 1 minute.
MorningEndRange = Pdate + Me.MorningEndRange - Int(Me.MorningEndRange) + 1 / 1440
EveningEndRange = Pdate + Me.EveningEndRange - Int(Me.EveningEndRange) + 1 / 1440

You can modify the code as follows to include 800 and 1500.