I need to generate a 8 digit Random Number in a Query

[Guest]

Hi,
I need to generate an 8 digit numeric "Activation Code" for a project I am
working on. I need this to be unique and random - but I need it auto
generated.
I don't care if it auto populates from a Field Property in the Table - or if
an Expression in a Query populates the field.

 

[Jeff Boyce]

You need a unique number. Does it have to be random? If the user never
sees the database, only his/her own Activation Code, could you just use an
Autonumber field?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"(e-mail address removed)"

 

[Guest]

Jeff,
Hey thanks for your answer. Sadly I already need an auto-number field for
Request Number - and access will only let me use 1 autonumber field per
table. I need an expression or formula I can drop into a query that will
create this munber.

 

[Jeff Boyce]

I guess I still am dense. If you already have an Autonumber ([Request
Number]) that you could use as your "Activation Code", why not use it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

lol, no your not dense.
The Main database is part of a Web Application (inventory related) which has
a GUI for entering orders manually - 1 by 1.
When done manually - 2 things besides the info manually enter happens.
1. A Request Number is entered. (Auto-number field) starting @ 1 - currently
below 300.
2. An activation code is created for the user to activate the device.

My problem is I have 1200 people I need to Bulk Load. The backend DB is an
MDB file which we can open/manipulate with Access. We copied the "Requests"
table into our seperate Bulk Load database. Then we created a query which
populates all the fields manually entered in the GUI. Well a bug not
mentioned in the vendors white papers - is that when you Bulk Load, (instead
of the manual GUI order entry) the Authorization code (8 digit numeric) is
not auto generated - that we will have to generate it manually. I was hoping
to find some sort of method within Access to do this. If it turns our to be a
nightmare, I'll just add 23456789 to the request number.

I was hoping to find a random number however, since the "device" serial
number are already sequential - I didn't want the activation codes to be also.

Don

I guess I still am dense. If you already have an Autonumber ([Request
Number]) that you could use as your "Activation Code", why not use it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Hey thanks for your answer. Sadly I already need an auto-number field for
Request Number - and access will only let me use 1 autonumber field per
table. I need an expression or formula I can drop into a query that will
create this munber.

 

[pietlinden]

Would this work?

Public Function GenerateID(ByVal intLength As Integer) As String
Dim intCounter As Integer
For intCounter = 1 To intLength
Randomize
GenerateID = GenerateID & CStr(Int((10 * Rnd)))
Next intCounter
End Function

Then you could call it in your form in the BeforeInsert event of your
form. If you need to update a table of these, you'd have to do it in a
recordset (I think), because when I tried it in a query, the function
gets called only once.

 

[John Spencer]

John Vinson posted most of this a while back. I've modified the function to
handle your specific case.

One problem is that this could generate duplicate numbers since it is
generating random numbers and does not check for the existence of a number
already in the set of records. Second problem is that the number generated
can be less then 8 digits since leading zeroes would get removed. You can
handle that by storing a string in a text field where you format the value
returned or you can change the code line assigning the value to read
something like the line below. That should take numbers smaller than the
required length and add zeroes to the end of the number. So if the number
generated was 1021 the function would return 10210000d (for eight digits)

RndInteger= CLng(LEFT(Int(Rnd() * 10 ^ vLength) &
String(vLength,"0"),vLength))


See http://support.microsoft.com/default.aspx?id=208855

Copy and paste this little function into a module; save the module as
basRandom (anything except RndInteger, you can't use the same name twice);


Public Function RndInteger(vIgnore as Variant, vLength as Integer) As Long
'vIgnore is required to force a query to call the function for every row
'vLength is required to specify the size of the integer.
'vLength must be restricted to the range 1-15
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndInteger= Int(Rnd() * 10 ^ vLength)
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndInteger([somefield],8)

Where "somefield" is any numeric field in your table - this just forces
Access to give you a new random number for every row. If you don't have a
numeric field available then you can use RndNum(Len([SomeField])) to force a
number to be generated.