Generating Random Numbers...

[Brad Pears]

The management team has come up with a plan to generate random job#'s for
each home that we build - However, the first two digits will be the year the
job came in i.e. this years jobs will be starting with "05" and the next
three digits will be the random number I generate.

This number is stored in our Access DB and I must make sure that each time a
new random number is generated, that it does not already exist in the db -
so will have to do a quick lookup.

Does anyone have any code to randomize numbers? I want to generate a 3
digit number.

Thanks,

Brad

 

[Martin J]

to use random it would look like this
dim job as int
Randomize
job = Rnd() * 1000
To check if its a double
if dlookup([job],

,"[job]=" & job) is null then add record
this is how it would be done. I personally hate doing it this way bec. why
use random numbers as keys? At least use counting numbers up to 999. Think
about what is going to happen when there are few keys left. It might take a
while for the program to find a key for you. You also have to make sure not
all the keys are used with a dcount() statment otherwise you'll be in an
infinite loop. With counting numbers its very simple nz(dmax(job,table),0)+1
if <1000 then add record. In all these cases the 05 should be added on the
form and not be saved with the field.

HTH
Martin J

 

[Sprinks]

Hi, Brad.

See VBA Help on the Randomize() and Rnd() functions. The former initializes
the random number generator, the second generates a random number. Rnd takes
a numeric argument to generate the random number. To ensure a different
number is returned, I suggest you use your last job number as the argument.

To convert the single precision decimal to a three digit integer, multiply
it by 1000, and take the integer portion:

Randomize()
Me![JobNumber] = Int(Rnd(MyArgument)*1000)

Hope that helps.
Sprinks

 

[Rob Oldfield]

Rather than doing the generation on the fly, it'd be better to just create
an initial table containing 1-999 in a random order, then just read those
numbers in order.

The word 'better' is, as Martin suggests, a relative term. You should tell
your management team that their plan is ludicrous (unless there are other
reasons you don't mention).

to use random it would look like this
dim job as int
Randomize
job = Rnd() * 1000
To check if its a double
if dlookup([job],

,"[job]=" & job) is null then add record
this is how it would be done. I personally hate doing it this way bec. why
use random numbers as keys? At least use counting numbers up to 999. Think
about what is going to happen when there are few keys left. It might take a
while for the program to find a key for you. You also have to make sure not
all the keys are used with a dcount() statment otherwise you'll be in an
infinite loop. With counting numbers its very simple nz(dmax(job,table),0)+1
if <1000 then add record. In all these cases the 05 should be added on the
form and not be saved with the field.

HTH
Martin J

The management team has come up with a plan to generate random job#'s for
each home that we build - However, the first two digits will be the year the
job came in i.e. this years jobs will be starting with "05" and the next
three digits will be the random number I generate.

This number is stored in our Access DB and I must make sure that each time a
new random number is generated, that it does not already exist in the db -
so will have to do a quick lookup.

Does anyone have any code to randomize numbers? I want to generate a 3
digit number.

Thanks,

Brad

 

[Christopher Glaeser]

The management team has come up with a plan to generate random job#'s for

each home that we build

Don't know if you are an Access beginner, so please excuse me for asking,
but does management want random or unique ids? The sequence 05-001, 05-002,
.... is the common practice and can be done with Autonum.

Best,
Christopher

 

[PC Datasheet]

One caveat is that autonumber does not guarantee that you will have every
number between 1 and 999!

 

[Mike Painter]

The management team has come up with a plan to generate random job#'s
for each home that we build - However, the first two digits will be
the year the job came in i.e. this years jobs will be starting with
"05" and the next three digits will be the random number I generate.

This number is stored in our Access DB and I must make sure that each
time a new random number is generated, that it does not already exist
in the db - so will have to do a quick lookup.

Does anyone have any code to randomize numbers? I want to generate a
3 digit number.

The assumption must be that you are building just a few houses and don't
want sequential nubmbers for some reason.
"Random" numbers that don't have duplicates are not random numbers by
definition.

One way to get pseudo random numbers that fit nicely into you 000 - 999 is
to create a table with one field with 000 - 999 scheme and a second field
with1000 autonumbers of type random, then sort on the random numbers.
Use dlookup with no criteria to get the next number and delete the top
record.