help creating random number

[smig]

I need some help creating a random numberit should be 15 characters long
can be any number from 100000000000000 to 999999999999999


Thanks,Tal

 

[Steve R.]

You may be interested in reviewing this post on random numbers.

 

[AUGuy]

If you're looking for a 1 line solution, you can also check the help file/example for 'Randomize'

 

[smig]

I created random numbers before using this code:

Dim intRnDmin As Integer
Dim intRndMax As Integer
Dim intRandom As Integer
 
intRnDmin = 1
intRndMax = 9
intRandom = Int((intRndMax - intRnDmin + 1) * Rnd + intRnDmin)

but when I try it now like this:

Dim snglRnDmin As Single
Dim snglRndMax As Single
Dim snglRandom As Single

snglRnDmin = 100000000000000#
snglRndMax = 999999999999999#
snglRandom = Int((snglRndMax - snglRnDmin + 1) * Rnd + snglRnDmin)

it give me a result of x.xxxx+E14
this is not what I need. I need the full 15 characters number

 

[Steve R.]

Try using either an integer or a long data type. However your number is too big to be either an integer or a long data type.

From MS Access help => "Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767."

From MS Access help =>"Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647"

Use a lower bound of 1 and an upper bound of 10 (or 1 through 999) and multiply the result by 100,000,000,000,000 (or 100,000,000,000). This of course would present a problem since the resulting number would still be too big for an integer or long number. You may be able to use one of the "Type Conversion Functions" to get a usable number. Alternatively you may want to rethink the value of the range of numbers that you want.

 

[smig]

I thought of using Long but as you also said Long is not long enough

I can't rethink of the created number as this is a MUST (by Tax authority).

the only solution I can think of is to concat two long numbers into one 15 characters long string

I'm not a mathematic expert but I think it will give the same number of random optins (they ask for a long random number to minimize the option of duplicates)

 

[Steve R.]

I can't rethink of the created number as this is a MUST (by Tax authority).

"PLAN B": Create a table with a field to hold the tax-id number. (To make this a bit easier you could have a second field that just holds a pseudo record number.) Count the number of records with dcount. Also implicit is that the tax-id number is identified by (associated with) the record number. Design your random number generator to create random numbers from 1 to the total number of tax-ids. So if record #5 is selected by the random number generator you pull out tax-id number ZZZZ.

Again take a look at my post on a method to implement Plan B. "The program works by creating a virtual jar (destination array) and filling it with virtual numbered beans. The beans are then randomly pulled out one at a time to create a numbered list." In your case, each bean is a "container" that holds a tax-id number.

 

[smig]

not sure I totaly understood your plan.
in any case, they require me to create a 15 characters long random number. this number must be pure random.

my plan works OK, and I'll stick with it. it's simple.
as I think of it concating 15 single 0-9 random numbers will give 10^15 options and it's exactly the same number of option as creating one number out of 1,000,000,000,000,000

 

[Steve R.]

they require me to create a 15 characters long random number. this number must be pure random.

I was afraid of getting that answer. I was expecting that you were randomly selecting a (15 digit) tax-id number rather than generating a random number of 15 digits.

Why is a fifteen digit random number necessary?
How is it being used?

 

[smig]

they need this random number to be a unique key for all files created on the same run

as I proved concating 15 times random numbers of 10 is the same result as creating a single random of 1000000000000000 it's not a problem doing it now.

just some statistics
throwing two dices at the same time is exactly as throwing the same dice twice.
people like to see a row of 10 boys, or 10 girls (you can even find these kind of things in the Guinness book), but statistecely it's the same as any other combination of 10 boys and girls.

thanks for the help and idead

 

[Steve R.]

Glad to have been a sounding board. Good luck.

 

[DCrake]

Another solution would be to create a new table called TblRandonNumbers

Add 2 fields

GUID - Auto Number - Format Replication ID - Primary Key

Temp - Boolean Yes/No field

When you want a new 15 digit number add a new record to the table which will create a random GUID which looks something like this

{01015B21-748A-4FF3-BEE3-ABD53AFB8A37}

Then write yourself a little function that extracts all the digits from the code

010152174843353837

Then use the first/last 15 digits

Public Function ExtractDigits(Anystring As String) As String

Dim i As Integer
Dim sStr As String

For i = 1 To Len(AnyString)
    If IsNumeric(Mid(AnyString,i,1)) Then
        sStr = sStr & Mid(AnyString,i,1)
    End If
Next

ExtractDigits = Left(sStr,15)

End Function

 

[smig]

thanks David for the idea.
here is the code used now

Dim snglRnDmin As Long
Dim snglRndMax As Long
Dim strRandom As String
Dim i As Integer
 
snglRnDmin = 0
snglRndMax = 9
strRandom  = ""
 
For i = 1 To 15
    strRandom = strRandom & Int((snglRndMax - snglRnDmin + 1) * Rnd + snglRnDmin)
Next i

simple and work like charm

 

[Steve R.]

Great solution! As a quick observation of your code. Do you wish to use the randomize statement just before executing the i = 1 to 15 statement?

From Access Help:
"Randomize uses number to initialize the Rnd function's random-number generator, giving it a new seed value. If you omit number, the value returned by the system timer is used as the new seed value."

"If Randomize is not used, the Rnd function (with no arguments) uses the same number as a seed the first time it is called, and thereafter uses the last generated number as a seed value."

 

[smig]

I guess I can simplify the code here to:
strRandom = strRandom & Int(10 * Rnd)



what is the idea of the Randomize statement? also I'm not sure I understand the seed idea.

this is from the help:

Rnd Function Example
This example uses the Rnd function to generate a random integer value from 1 to 6.

Dim MyValue
MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.


Randomize Statement Example
This example uses the Randomize statement to initialize the random-number generator. Because the number argument has been omitted, Randomize uses the return value from the Timer function as the new seed value.

Dim MyValue
Randomize ' Initialize random-number generator.
MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.

with or without the Randomize both generate the same result



what is the length of the original random number ?

 

[Rabbie]

what is the length of the original random number ?

The Rn function returns a pseudo-random real number between 0 and 1.

The randomise function is used to seed the Rnd function so it will generate different results each time it runs

 

[smig]

won't the Rnd function generate different results each time it runs without the Randomize ?
my tests show it does.

 

[Rabbie]

won't the Rnd function generate different results each time it runs without the Randomize ?
my tests show it does.

Did you do the tests in the same session or in different sessions?

 

[smig]

I use this code and get 15 characters long number, that at least looks random. every run will create a different set of numbers.

Dim snglRnDmin As Long
Dim snglRndMax As Long
Dim strRandom As String
Dim i As Integer
 
snglRnDmin = 0
snglRndMax = 9
strRandom  = ""
 
For i = 1 To 15
    strRandom = strRandom & Int((snglRndMax - snglRnDmin + 1) * Rnd + snglRnDmin)
Next i

If I want to add the randomize statment to make sure it's random where should it be put ?

 

[Rick_A]

For a 15 character random number try this:

Public Function GetRandom() As Double
Dim low As Double
Dim hi As Double

low = 100000000000000#
hi = 999999999999999#

GetRandom = CDbl((hi - low + 1) * Rnd + low)
End Function