Random Number in Query

  • Follow


Hi.  I am trying to get a Random number between 0 and 5 to appear on each row 
of a query.  I can get the same number on each row (it changes every time I 
run the query) but cannot get a different random number for each row.

Basically, I want to create some Test data by adding the Random number to a 
Received Date and then saving this new date as a Completed Date.

I have created a Module to generate a Random number:

Function Random_Number() As Integer

    Randomize
    Random_Number = Int(Rnd * 6)

End Function


Then, I call the function from a query:

Field = Number: Random_Number()

Can anyone tell me what I am doing wrong?

Cheers,
Steve.

0
Reply Utf 1/3/2008 10:51:01 AM

Sorry, Ive just answered my own question!!

I used my Unique ID field in the Rnd command:

RandomNumber : Int(Rnd([Workflow_Ref])*6)

And that gives me a value between 0 and 5 for adding onto my Received date.

Thanks Me :)



"FBxiii" wrote:

> Hi.  I am trying to get a Random number between 0 and 5 to appear on each row 
> of a query.  I can get the same number on each row (it changes every time I 
> run the query) but cannot get a different random number for each row.
> 
> Basically, I want to create some Test data by adding the Random number to a 
> Received Date and then saving this new date as a Completed Date.
> 
> I have created a Module to generate a Random number:
> 
> Function Random_Number() As Integer
> 
>     Randomize
>     Random_Number = Int(Rnd * 6)
> 
> End Function
> 
> 
> Then, I call the function from a query:
> 
> Field = Number: Random_Number()
> 
> Can anyone tell me what I am doing wrong?
> 
> Cheers,
> Steve.
> 
0
Reply Utf 1/3/2008 11:00:01 AM


hi Steve,

FBxiii wrote:
> Hi.  I am trying to get a Random number between 0 and 5 to appear on each row 
> of a query.  I can get the same number on each row (it changes every time I 
> run the query) but cannot get a different random number for each row.
The query optimizer handles all functions as deterministic. So a 
function is only called again when its parameters are different.

> Function Random_Number() As Integer
Make it

   Public Function Random_Number(ADummy As Long) As Integer

> Then, I call the function from a query:
> Field = Number: Random_Number()
And use it

   Number: Random_Number([ID])

where [ID] must be an auto-increment field.


mfG
--> stefan <--
0
Reply Stefan 1/3/2008 11:04:24 AM

2 Replies
1158 Views

(page loaded in 0.177 seconds)


Reply: