Generate a Random Number (1 Viewer)

I would like to add a field in a querry with a random number of 1,2 or 3. I have looked at RND function but it doesn't seem to let you specify a range of numbers to use.

Thanks

You will do better to stick with the other thread you started as you already have a response

http://www.access-programmers.co.uk/forums/showthread.php?p=787000#post787000

rnd returns a decmal fraction of a number ie less than 1

so if you want a number in the range 1 to 500 say, multiply the result by 500
this now gives you a number in the range 0.something to 499.something

so now, truncate the number by using a clng, or cint, - now you have a number in the range 0 to 499, so add 1 to the result to get somethig in the range 1 to 500

this is all encapsualted in

myrandomnumber = clng(rnd()*500+1)

Thats works great, but it gives the same random number to all the records that have been querried. Is there any way to issue a different random number to each record in the queery?

Thanks

i think the command

randomize

does that

Rnd is basically a Randomize(Now()). You need to use a seed to generate a random number.

Some good seeds would be anything that cause an irrational and transcendental numbers. Some examples of irrational & transcendental numbers: pi, Euler's constant, and square root of 2.

The would be a couple of ways to do it but both of these are crude.

If I understand your problem you want the records to be equally split between having a 1, 2 or 3 in a field. If so then

If it is for a bulk of records then fill a field with randon numbers. To do that just make a field that has Rnd() as the default. A append across without that field being involed will do that. I can probably oull a a couple of queries out I use to fill a field with randon numbers. I use it for telemarketing so as to sort prospects in random order

Now sort the randon field in A to Z

Now make a simple macro that has these action lines

SetValue a 1
NextRecord
SetValue a 2
NextRecord
SetValue a 3
Next Record

That macro is run from another macro with RunMacro action and it stops at the end of the record set with [Field] Is Not Null. RunMacro stops when the condition equates to false. Alternatively use the Repeat count and equal to the number of records. In the macro that has run macro have as its fist action line GoToFirst record.

At the start of each of the two macros Have Echo Yes.

To each record on "enter new record basis" place an unbound textbox on the form with a default entry of 1. Do a set value action with conditions of If Textbox is 1 then enter 1 in the field and reset the value of the unbound box to 2. The conditions you do then mean the next record will get a 2 and th unbound will be set to a 3 and so on.

That will obviously only give what you want if entering several records at a time. If you tend to only enter only one record at a time then base it on the last record that was entered. That is get the value of your field that has the 1, 2 or 3 for the last record entered. You could just make a little form that will open with the last record and SetValue the unbound text box with your last value of 1, 2 or 3

PS,

I looked at the queries and how I do the random.

Firstly, there is a second table made that has the ID field and random number field, they match the field names of the maintable. The ID field on the second table is a primary key.

The first query that runs deletes all records from the second table.

The second query appends the ID field from the main table to the second table.

The third query is an update query and updates the field used for random numbers in the main table with the randon mumbers generated in the second table.

Thanks for that, I understand the logic bit I am struggling with the macro. I get the error:

Microsoft Access can't find the form 'Table1' referred to in a macro expression or Visual Basic code.

The string in the SetValue Item field is [Table1]![random]

Is because you cannot update with SetValue directly to a field in a table?

To get the macro to go down the records for the 1, and 3 place it on the form. The form must be open.

Just make a simple continuous form and stick the macro on the header for the OnClick event. Use either a button or a stand alone label.

And make sure you have a "stop" for the macro such as the Repeat Count or the condition, usuall something like [IDField] Is Not Null. Remember that the condition that stops RunMacro is based on it equating to false, hence Is Not Null. That condition stops it when it tries to go past the last record. If you don't have the stop then the macro will run forever as it will keep creating a new record...it will finish at China