Recalculating a function

[Heapy]

I'm creating a query in which I need Access to give me a random number;
problem is when I create the function to generate a random number, Access
uses the same result for every row/record returned.

e.g.
Function RandomNumber()

Randomize
RandomNumber = Rnd()

End Function


When I call the function, the random number changes every time I re-run the
query, but the same number appears for each record/row.

In xl, there's the Volatile method which forces the re-calculation of the
function everytime it's called. Does Access have a similar method?

Or, is there some other way to accomplish this?

Thx
HWH

 

[John Spencer (MVP)]

When you call the function in a query you need to pass it a number field to
force access to call it for each row. If you don't Access will think that it
only needs to be called once.

Select RandomNumber([YourTable].[SomeNumberField]) as RndmNr
FROM YourTable

 

[Heapy]

Thanks a million John...
HWH

When you call the function in a query you need to pass it a number field to
force access to call it for each row. If you don't Access will think that it
only needs to be called once.

Select RandomNumber([YourTable].[SomeNumberField]) as RndmNr
FROM YourTable

I'm creating a query in which I need Access to give me a random number;
problem is when I create the function to generate a random number, Access
uses the same result for every row/record returned.

e.g.
Function RandomNumber()

Randomize
RandomNumber = Rnd()

End Function

When I call the function, the random number changes every time I re-run the
query, but the same number appears for each record/row.

In xl, there's the Volatile method which forces the re-calculation of the
function everytime it's called. Does Access have a similar method?

Or, is there some other way to accomplish this?

Thx
HWH