Generating Random Numbers...

  • Thread starter Thread starter Brad Pears
  • Start date Start date
B

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
 
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
 
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
 
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).


Martin J said:
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

Brad Pears said:
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
Click to expand...
Click to expand...
 
The management team has come up with a plan to generate random job#'s for
each home that we build
Click to expand...

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
 
One caveat is that autonumber does not guarantee that you will have every
number between 1 and 999!
 
Brad said:
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.
Click to expand...
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.
 
No they want random numbers. We currently use a concurrent numbering system,
but mgmt does not want this for reasons only mgmt really knows. They want
random numbers - presumably to hide the number if jobs we are getting a
year...
 
Can you explain in a bit more detail what you mean by the following
statement?? I may be missing something - don;t want to do that!

"You also have to make sure not
all the keys are used with a dcount() statment otherwise you'll be in an
infinite loop."

Thanks,

Brad


Martin J said:
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

Brad Pears said:
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
Click to expand...
Click to expand...
 
That's also a good plan. Basically they do not want anyone to know how many
jobs we have on the go. It could compromise a deal if another log home
company sees what our job# might be.. They might be in a position to
undercut us knowing we have many jobs etc... or take a deal from us anyway
they can, knowing that we haven;t done much this year... know what I'm
getting at???
Rob Oldfield said:
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).


Martin J said:
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
Click to expand...
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
Click to expand...
year
the
Click to expand...
Click to expand...
 
Back
Top