Random function gives same value every time when updating a table.

[BTilson]

I have a function that I wrote that takes a clock in time and a clock out time as paramaters and is supposed to return the amount of break time that is allowed between those two times. That function, while tricky, is done, I think. However, I need to test it thoroughly.

I decided to write a function to populate a table with random clock in and out values. The function is relatively simple, and it works perfectly in the immediate window. I ran it in a loop probably 30,000-40,000 times, and it gave sufficiently random times for my needs. However, when I use this function in an update query, it sets the generated times to the exact same thing for every record. If I re-run the update query, the table will have different times than before, but still the same two for every record.

Here is my random time function:

Public Function generateRandomTime()

    Randomize Timer
    
    h = CInt(Rnd() * 12) + 1
    m = CInt(Rnd() * 59)
    s = CInt(Rnd() * 59)
    ampm = CInt(Rnd() * 2) + 1
    
    timeHolder = h & ":" & m & ":" & s
    If ampm = 1 Then timeHolder = timeHolder & " AM"
    If ampm = 2 Then timeHolder = timeHolder & " PM"
    
    timeToReturn = CDate(timeHolder)

    generateRandomTime = timeToReturn

End Function

And here is the SQL for my update query.

UPDATE Indirect SET Indirect.CLOCK_IN = generateRandomTime(), Indirect.CLOCK_OUT = generateRandomTime();

Should be pretty straight forward?

Anyone have any ideas?

Thanks very much in advance!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[PHV]

Define a dummy parameter in the function declaration and call it in the query with a field name as parameter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BTilson]

Brilliant. Works perfectly.

Thanks.

Brooks Tilson
Database Development
Tilson Machine, Inc.