Get Random Date to return per record in Query

[gumby]

Query

SELECT tblStaff.StaffName, RandomDateInRange(#1/1/2001#,#12/31/2001#)
AS DateOne
FROM tblStaff
GROUP BY tblStaff.StaffName;

Module

Public Function RandomDateInRange(LowerDate As Date, UpperDate As
Date) As Date
Randomize
RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
End Function

Currently this query returns a random date, however it is the same one
for the entire query. How can I get the each record in the query to
have a seperate random date?

I am trying to generate a random date for each staff member for QA
purposes.

Thanks,
David

 

[John W. Vinson]

Query

SELECT tblStaff.StaffName, RandomDateInRange(#1/1/2001#,#12/31/2001#)
AS DateOne
FROM tblStaff
GROUP BY tblStaff.StaffName;

Module

Public Function RandomDateInRange(LowerDate As Date, UpperDate As
Date) As Date
Randomize
RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
End Function

Currently this query returns a random date, however it is the same one
for the entire query. How can I get the each record in the query to
have a seperate random date?

I am trying to generate a random date for each staff member for QA
purposes.

Thanks,
David

Unless you pass the function an argument referencing a table field, Access
will "save time" by calling the function only once and reusing the result. The
function doesn't need to *do* anything with the passed value, it just needs to
be in the argument list. For instance:

SELECT tblStaff.StaffName,
RandomDateInRange(#1/1/2001#,#12/31/2001#,[StaffName])
AS DateOne
FROM tblStaff
GROUP BY tblStaff.StaffName;

Public Function RandomDateInRange(LowerDate As Date, _
UpperDate As Date, _
vIgnore As Variant) As Date
Static bRandomize As Boolean
If bRandomize = False Then
Randomize
bRandomize = True
End If
RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
End Function

I'd also suggest just randomizing once per Access session. as noted in the
code.

Just to be more of a purist about not mixing datatypes, you might want to
consider:

RandomDateInRange = DateAdd("d", Int((UpperDate - LowerDate + 1) * Rnd),
LowerDate)

John W. Vinson [MVP]

 

[Marshall Barton]

Query

SELECT tblStaff.StaffName, RandomDateInRange(#1/1/2001#,#12/31/2001#)
AS DateOne
FROM tblStaff
GROUP BY tblStaff.StaffName;

Module

Public Function RandomDateInRange(LowerDate As Date, UpperDate As
Date) As Date
Randomize
RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
End Function

Currently this query returns a random date, however it is the same one
for the entire query. How can I get the each record in the query to
have a seperate random date?

The problem is that
RandomDateInRange(#1/1/2001#,#12/31/2001#)
is the same for all records so Access/Jet only needs to
evaluate it once.

Add another argument to the function:

Public Function RandomDateInRange(LowerDate As Date,
UpperDate As Date, junk As Variant) As Date

And call it with a field in the extra argument:
RandomDateInRange(#1/1/2001#, #12/31/2001#, StaffName)
to make Access/Jet think the function can be different for
different records.

 

[gumby]

Query

SELECT tblStaff.StaffName, RandomDateInRange(#1/1/2001#,#12/31/2001#)
AS DateOne
FROM tblStaff
GROUP BY tblStaff.StaffName;

Public Function RandomDateInRange(LowerDate As Date, UpperDate As
Date) As Date
Randomize
RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
End Function

Currently this query returns a random date, however it is the same one
for the entire query. How can I get the each record in the query to
have a seperate random date?

I am trying to generate a random date for each staff member for QA
purposes.

Thanks,
David

Unless you pass the function an argument referencing a table field, Access
will "save time" by calling the function only once and reusing the result. The
function doesn't need to *do* anything with the passed value, it just needs to
be in the argument list. For instance:

SELECT tblStaff.StaffName,
RandomDateInRange(#1/1/2001#,#12/31/2001#,[StaffName])
AS DateOne
FROM tblStaff
GROUP BY tblStaff.StaffName;

Public Function RandomDateInRange(LowerDate As Date, _
UpperDate As Date, _
vIgnore As Variant) As Date
Static bRandomize As Boolean
If bRandomize = False Then
Randomize
bRandomize = True
End If
RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
End Function

I'd also suggest just randomizing once per Access session. as noted in the
code.

Just to be more of a purist about not mixing datatypes, you might want to
consider:

RandomDateInRange = DateAdd("d", Int((UpperDate - LowerDate + 1) * Rnd),
LowerDate)

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Thank you, Works great.

 

[gumby]

Query

SELECT tblStaff.StaffName, RandomDateInRange(#1/1/2001#,#12/31/2001#)
AS DateOne
FROM tblStaff
GROUP BY tblStaff.StaffName;

Public Function RandomDateInRange(LowerDate AsDate, UpperDate As
Date) AsDate
Randomize
RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
End Function

Currently this query returns arandomdate, however it is the same one
for the entire query. How can I get the eachrecordin the query to
have a seperaterandomdate?

The problem is that
RandomDateInRange(#1/1/2001#,#12/31/2001#)
is the same for all records so Access/Jet only needs to
evaluate it once.

Add another argument to the function:

Public Function RandomDateInRange(LowerDate AsDate,
UpperDate AsDate, junk As Variant) AsDate

And call it with a field in the extra argument:
RandomDateInRange(#1/1/2001#, #12/31/2001#, StaffName)
to make Access/Jet think the function can be different for
different records.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Is their anyway to get this to ignore weekends (Sat & Sun)?

Thanks,
David

 

[Marshall Barton]

Is their anyway to get this to ignore weekends (Sat & Sun)?

Public Function RandomDateInRange( _
LowerDate AsDate, _
UpperDate AsDate, _
junk As Variant _
) AsDate
Static Initialized As Boolean
Dim dt As Date

If Not Initialized Then Randomize : Initialized = True

Do
dt = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
Loop Until DatePart("w", dt, vbMonday) < vbSaturday

RandomDateInRange = dt
End Function

 

[gumby]

Is their anyway to get this to ignore weekends (Sat & Sun)?

Public Function RandomDateInRange( _
LowerDate AsDate, _
UpperDate AsDate, _
junk As Variant _
) AsDate
Static Initialized As Boolean
Dim dt As Date

If Not Initialized Then Randomize : Initialized = True

Do
dt = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
Loop Until DatePart("w", dt, vbMonday) < vbSaturday

RandomDateInRange = dt
End Function

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thank you, it works great.I did have to change to vbFriday because it
keep generating some Saturdays.

David

 

[Marshall Barton]

Thank you, it works great.I did have to change to vbFriday because it
keep generating some Saturdays.

Arrggghhh, you're right. At the last minute, I changed the
6 I originally had to vbSaturday. Since vbSaturday has the
value 7, it was clearly an incorrect change. Just to make
the code less confusing, I suggest that instead of vbFriday,
you use either 6 or vbSaturday-1

Sorry about the confusion.

 

[gumby]

Thank you, it works great.I did have to change to vbFriday because it
keep generating some Saturdays.

Arrggghhh, you're right. At the last minute, I changed the
6 I originally had to vbSaturday. Since vbSaturday has the
value 7, it was clearly an incorrect change. Just to make
the code less confusing, I suggest that instead of vbFriday,
you use either 6 or vbSaturday-1

Sorry about the confusion.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Would it be possible to pick a month and have the query return two
random dates from each week? What about ensuring the two random dates
are never the same?

 

[Marshall Barton]

Would it be possible to pick a month and have the query return two
random dates from each week? What about ensuring the two random dates
are never the same?

Since a function can return only one value for one record,
that would get pretty tricky.

First, you would need a record for each date. This can be
done by creating a table (named Numbers) with one field
(named Num) and populating itsrecords with 1, 2, 3, ... up
to more than you will ever want.
Then your query would use a cross product Join something
like:

SELECT StaffName,
RandomDateInRange(#1/1/2001#,#12/31/2001#, Num)
AS DateOne
Num
FROM tblStaff, Numbers
WHERE Num <= 8

Then you will need to modify the function ti use the 3rd
argument to determine the week.

I don't have time to try to work it all out for you now and
I will be unavailable until Monday or Tuesday so see how far
you can get in the meantime. Post back sometime Monday with
what you've got and what's blocking you.

 

[gumby]

Since a function can return only one value for one record,
that would get pretty tricky.

First, you would need a record for each date. This can be
done by creating a table (named Numbers) with one field
(named Num) and populating itsrecords with 1, 2, 3, ... up
to more than you will ever want.
Then your query would use a cross product Join something
like:

SELECT StaffName,
RandomDateInRange(#1/1/2001#,#12/31/2001#, Num)
AS DateOne
Num
FROM tblStaff, Numbers
WHERE Num <= 8

Then you will need to modify the function ti use the 3rd
argument to determine the week.

I don't have time to try to work it all out for you now and
I will be unavailable until Monday or Tuesday so see how far
you can get in the meantime. Post back sometime Monday with
what you've got and what's blocking you.

Marsh,

Sorry I was out all last week and the week before. I setup the table
and messed with the query for a little while. I keep getting errors
about type mismatch or mispelled.

I am not sure what a cross product join query is.

David

 

[gumby]

Marsh,

Sorry I was out all last week and the week before. I setup the table
and messed with the query for a little while. I keep getting errors
about type mismatch or mispelled.

I am not sure what a cross product join query is.

David- Hide quoted text -

- Show quoted text -

Marsh,

Ok, I think I got the query to work.

SELECT qryStaffName.StaffName, Numbers.Num,
RandomDateInRange(#1/1/2001#,#12/31/2001#,[Num]) AS [Date]
FROM qryStaffName, Numbers
WHERE (((Numbers.Num)<=8));

Not sure how to change the function.

Public Function RandomDateInRange(LowerDate As Date, UpperDate As
Date, junk As Variant) As Date
Static Initialized As Boolean
Dim dt As Date
If Not Initialized Then Randomize: Initialized = True
Do
dt = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
Loop Until DatePart("w", dt, vbMonday) < vbFriday
RandomDateInRange = dt
End Function
David

 

[Marshall Barton]

Ok, I think I got the query to work.

SELECT qryStaffName.StaffName, Numbers.Num,
RandomDateInRange(#1/1/2001#,#12/31/2001#,[Num]) AS [Date]
FROM qryStaffName, Numbers
WHERE (((Numbers.Num)<=8));

Not sure how to change the function.

Public Function RandomDateInRange(LowerDate As Date, UpperDate As
Date, junk As Variant) As Date
Static Initialized As Boolean
Dim dt As Date
If Not Initialized Then Randomize: Initialized = True
Do
dt = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
Loop Until DatePart("w", dt, vbMonday) < vbFriday
RandomDateInRange = dt
End Function

I've been away for a while too.
The function needs to use the Num argument to determine
which week to calculate. However, because there is no good
correlation between weeks and months, I will assume that the
LowerDate is the Monday of the first week.

Public Function RandomDateInRange(LowerDate As Date, _
UpperDate As Date, _
Num As Variant
) As Date
Static Initialized As Boolean
Dim dt As Date

If Not Initialized Then Randomize: Initialized = True

dt = LowerDate + Int(4.99 * Rnd) + 7 * ((Num - 1) \ 2)

RandomDateInRange = dt
End Function

I can't think of a way to prevent duplicates because the Num
values can not be guaranteed to be in sequential order.