fill field with random numbers

[Locky]

Hi

I want to fill several fields in access with random numbers in mod 10,
based on whether another field is empty or not.

I use the following query:


UPDATE tbl_Results SET Q11_17 = (Int((100000-50+1)*Rnd()+100000) Mod
20)*10
WHERE Q8_17<>'' OR Q8_17 IS NOT NULL;

If I run this query, every field in Q11_7 which meets the criteria is
filled with the same random number.

How do I re-initialise the random number before the query is exectuted
against the next field?

Thanks
LockyBoy

 

[Dirk Goldgar]

Hi

I want to fill several fields in access with random numbers in mod 10,
based on whether another field is empty or not.

I use the following query:


UPDATE tbl_Results SET Q11_17 = (Int((100000-50+1)*Rnd()+100000) Mod
20)*10
WHERE Q8_17<>'' OR Q8_17 IS NOT NULL;

If I run this query, every field in Q11_7 which meets the criteria is
filled with the same random number.

How do I re-initialise the random number before the query is exectuted
against the next field?

Thanks
LockyBoy

Pick some field in tbl_Results that will always be positive number --
not Null, 0, or negative -- and pass that field to the Rnd() function.
That way the query engine will recognize that this is not a constant
expression, and evaluate it for each record. Your query might look
like:

UPDATE tbl_Results
SET Q11_17 = (Int((100000-50+1)*Rnd([ID])+100000) Mod 20)*10
WHERE Q8_17<>'' OR Q8_17 IS NOT NULL;

(assuming "ID" is the name of a field such as I described).

If you don't have such a field in the table, there's a way around it,
but this is the simplest solution.

 

[John Vinson]

Hi

I want to fill several fields in access with random numbers in mod 10,
based on whether another field is empty or not.

I use the following query:


UPDATE tbl_Results SET Q11_17 = (Int((100000-50+1)*Rnd()+100000) Mod
20)*10
WHERE Q8_17<>'' OR Q8_17 IS NOT NULL;

If I run this query, every field in Q11_7 which meets the criteria is
filled with the same random number.

How do I re-initialise the random number before the query is exectuted
against the next field?

Access is being "helpful" by calling the Rnd() function once; since
the function has no arguments, the compiler assumes that it will
return the same value for every row anyway.

The trick is to force it to call Rnd() every record. One way to do
this is to use the fact that passing Rnd() a positive number has the
same effect as passing no argument (a negative argument sets the seed
value for the random number algorithm). Replace Rnd() by Rnd([field])
where [field] is any non-negative number field in the table.

John W. Vinson[MVP]