Update w/Random Dates: A97 vs A2003

[raskew]

I'm attempting to update (populate) a null date/time field with random dates using:

Public Function GenRndDate(ByVal Upper As Date, ByVal Lower As Date) As Date
    Randomize
    GenRndDate = Int((Upper - Lower + 1) * Rnd + Lower)
End Function

...called from this Update Query:

UPDATE tblDateTest4 SET tblDateTest4.MyDate = GenRndDate(#1/1/2007#,#1/1/2009#)
WHERE
   (((tblDateTest4.MyDate) Is Null));

Problem being: It works as advertised in A97, but not in A2003 (processes just fine, but doesn't populate the field).

I'm clueless. Any thoughts?

Thanks,

Bob

 

[pbaldy]

Not sure Bob. I have 2003 here, and it updated fine. I did have to add a seed to the Randomize statement to get truly random values. As written, it updated every record with the same value.

 

[raskew]

Paul -

Thanks for that. I changed Randomize to Randomize (seed), which is supposed to be the default anyway, and it did populate the fields but all with the same date.

Wound you mind showing how you modified my code.

Thanks, Bob

 

[pbaldy]

I changed the function to use an ID field in the data:

Public Function GenRndDate(ByVal Upper As Date, ByVal Lower As Date, lngID As Long) As Date
    Randomize (lngID)
    GenRndDate = Int((Upper - Lower + 1) * Rnd + Lower)
End Function

and obviously added that to the call:

GenRndDate(#1/1/2007#,#1/1/2009#, IDField)

 

[raskew]

Yes! That did it (along with reversing the order of dates #1/1/2007#, #1/1/2009# in order to correspond with Upper and Lower (duh huh).

Thanks for that.

Bob