update query / RND() question

[Vsn]

Hello all,

I found something I did not expect, while using the below query i found that
all records end up with the same number.

UPDATE tblNewFAQ SET tblNewFAQ.fRND = Rnd();

Does the query only once get a value from the RND function? I have also
tried if with a function RNDNumber() to make sure it used Randomize, this
gives the same result; all records get the same number.

UPDATE tblNewFAQ SET tblNewFAQ.fRND = NumberRND();

Function NumberRND()
On Error Resume Next
Randomize
NumberRND = Int((1000 * Rnd) + 1)
End Function

How could I do it in a way every record will get its own calculated value?

Thx,
Ludovic

 

[Allen Browne]

The query optimizer notices that no arguments are passed to the function, so
it figures the function will return the same value every time. It therefore
doesn't bother to call it for every row the the query.

You can fool it by passing in a numeric value (typically the autonumber.) Of
course, Rnd() doesn't need the value, but at least it gets called for each
row. This kind of thing:
UPDATE tblNewFAQ SET tblNewFAQ.fRND = Rnd(tblNewFAQ.ID);

 

[Vsn]

Thx, Allen this did the trick.

Ludovic

The query optimizer notices that no arguments are passed to the function,
so it figures the function will return the same value every time. It
therefore doesn't bother to call it for every row the the query.

You can fool it by passing in a numeric value (typically the autonumber.)
Of course, Rnd() doesn't need the value, but at least it gets called for
each row. This kind of thing:
UPDATE tblNewFAQ SET tblNewFAQ.fRND = Rnd(tblNewFAQ.ID);