Updating With Calculation

[imsco]

I need to update a field with a range of random numbers, say between 50,000 and 500,000. How would I do this?

 

[DotNetGnat]

use Rnd() function...

-DNG

 

[imsco]

hmmm....so it would look like how with the range?

Rnd(50000-500000)????

 

[DotNetGnat]

something like this:

expr1: Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

replace your lower and upperbounds

-DNG

 

[imsco]

this replaced all the records with the same value...need to be random numbers for each record.

 

[Golom]

SQL is optimizing your query and is calling Rnd only once. You need to trick it into making the call for each record retrieved. First write a function

Public Function MyRnd(x As Variant) As Long
    MyRnd = (500000 - 50000) * Rnd + 50000
End Function

Then write your SQL as

SELECT myTable.myField, MyRnd(myTable.myField) AS RandomNumber
FROM myTable;

Where you pass any field from your record into the function. It doesn't really matter which one because (as you can see) the value isn't used in the function. It's purpose is to force SQL to evaluate the function for every record.