Restrain random number to positive numbers

[Guest]

I created a table with an autonumber field which in Design view I set the New
Values property to Random and I am not allowing duplicates.

When it is generating the random number it is producing some negative numbers.

Is there a way to constrain this to only positive numbers.

I wanted to put abs(random) in the New Values property but it would not let
me.

Any help would be appreciated.

 

[Douglas J. Steele]

There's no way of limiting the value generated for an AutoNumber field.

Of course, its value shouldn't matter. The only reason AutoNumbers exist is
to provide an (almost guaranteed) unique value that can be used as a Primary
Key. Negative numbers fill that need as well as positive numbers. If you're
trying to apply some meaning to the value of an AutoNumber field, then an
AutoNumber field is probably an inappropriate choice.

 

[Guest]

I need to have a random number in the geneticIDnum field, it doesn't have to
be an autonumber. So is there a way to make a random number in VB code? If
there is how would you put the random number into the geneticIDnum field.

 

[Douglas J. Steele]

Take a look at the Rnd function.

To produce random integers in a given range, use this formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Before calling Rnd, use the Randomize statement without an argument to
initialize the random-number generator with a seed based on the system
timer.

You should also probably add code to check whether the value generated has
already been used, and generate another value if it has.

 

[Guest]

Thanks for the help that will work out.
I also need to know how you reference a field in a table to check its value.
What is the format? Is it like [tablename].[fieldname].value I am just
unsure on how to refer to a value in a field.

 

[Douglas J. Steele]

Easiest way is to use DLookup or DCount.

I'd probably do something like:

lngPossibleID = Rnd

Do While DCount("*", "MyTable", "ID = " & lngPossibleID) > 0
lngPossibleID = Rnd
Loop




--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for the help that will work out.
I also need to know how you reference a field in a table to check its value.
What is the format? Is it like [tablename].[fieldname].value I am just
unsure on how to refer to a value in a field.

Take a look at the Rnd function.

To produce random integers in a given range, use this formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Before calling Rnd, use the Randomize statement without an argument to
initialize the random-number generator with a seed based on the system
timer.

You should also probably add code to check whether the value generated has
already been used, and generate another value if it has.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


have
to code?
If exist
is then
an set
the would
not