Non-repeating random numbers

[TC]

I would like to have a query that spits out random, non-
repeating approval codes for a potential tracking
database.

How can that be put together?

Thanks

 

[TC]

Hi TC #2!

Please be aware that I have posted to various Access newsgroups under the TC
acronym for several years.

No-one has sole rights to any acronym. But it will be confusing for all
concerned, if both of us use it.

Could you maybe use TC #2 or somesuch?

Thanks
TC

 

[Scott McDaniel]

You can set an Autonumber field in a table to generate random numbers,
instead of the incremental numbers commonly used. You could build a table
with one field (the AutoNumber field) and base your query on that.

 

[Dirk Goldgar]

I would like to have a query that spits out random, non-
repeating approval codes for a potential tracking
database.

If they're non-repeating, they're not even close to truly random.
However, if you want "unguessable", non-repeating codes, it's possible
provided that you record each code chosen so that you can make sure you
don't generate it again. What are the desired characteristics of these
codes? Are they to be numbers (see the Rnd() function in the VB online
help), strings of characters, or what?

 

[TC]

Unless you're going to post something really brilliant. Then you should
feel free to post as TC #1. <g>

Absolutely! And if I am going to post something nasty (eg. to the imbecile
roger), I will post as TC #2!

TC (#1)

 

[Dirk Goldgar]

Hi TC #2!

Please be aware that I have posted to various Access newsgroups under
the TC acronym for several years.

No-one has sole rights to any acronym. But it will be confusing for
all concerned, if both of us use it.

Could you maybe use TC #2 or somesuch?

Unless you're going to post something really brilliant. Then you should
feel free to post as TC #1. <g>

 

[TCD]

Yes, what we are attempting is basically a five character
ID, 10000 - 99999, that will appear to be random. That
way our staff doesn't catch on to any patterns and
attempt to use false approval codes.

 

[TCD]

Sure, I dont' care what you call me as long as we can get
our database going.

 

[TCD]

Sounds like an easier solution. How do you set that up? I
can't get the one in the knowledge base to work. Thanks

 

[TCD]

Never mind. I got it now. Thanks!

-----Original Message-----
Sounds like an easier solution. How do you set that up? I
can't get the one in the knowledge base to work. Thanks

.

 

[Dirk Goldgar]

Yes, what we are attempting is basically a five character
ID, 10000 - 99999, that will appear to be random. That
way our staff doesn't catch on to any patterns and
attempt to use false approval codes.

Except for the non-reuse restriction, you could use a VBA function like
this to return a pseudo-random approval code in the range 10000 - 99999:

'----- start of code -----
Function NewApprovalCode() As Long

Dim lngCode As Long
Static blnRandomized As Boolean

If Not blnRandomized Then
Randomize
blnRandomized = True
End If

lngCode = Int((Rnd() * 90000)) + 10000

NewApprovalCode = lngCode

End Function

'----- end of code -----

I designed the routine to return a number value, rather than a string,
but it could easily be modified to return the code as a 5-digit
character string. The modified lines would be just:

Function NewApprovalCode() As String

and

NewApprovalCode = Format(lngCode, "00000")

However, the non-reuse issue isn't quite so simple. Do you really need
to check that no new approval code has already been used? If so, you
must have a table somewhere in which the approval codes are stored, and
thus you'll need to do a lookup in the function to verify that the code
that was just generated hasn't been used already, and loop until you
come up with one that hasn't. Or else, you could put a unique index on
that field in the table, and let the attempt to save the record generate
an error. But I don't know enough about the context in which you'd be
doing this to give you advice as to the best way to do it.

Note that random numbers in a range of only 90000 possibilities are
going to generate a substantial number of collisions. I hope you're not
planning to generate many of these.

 

[TCD]

Okay, maybe you can help me here then. I found a way to
do two fields with the primary key set as random. When I
add a third field though, it gives me a run time error on
the next to last line.

Private Sub cmdInsert_Click()

Dim db As DAO.Database
Dim strSQL As String

Randomize

Set db = CurrentDb

strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(Code, Name)"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ",""" & Me!Name & """);"



Debug.Print strSQL

db.Execute strSQL

db.Close

End Sub

 

[TC]

Ok, thanks!

TC

Sure, I dont' care what you call me as long as we can get
our database going.

 

[TC]

'Name' is a reserved word in Access. These should normally be enclosed in
square brackets. Perhaps try:

strSQL = strSQL & "(Code, [Name])"
and:

strSQL = strSQL & ",""" & Me![Name] & """);"

HTH,
TC

 

[Tim Ferguson]

Yes, what we are attempting is basically a five character
ID, 10000 - 99999, that will appear to be random. That
way our staff doesn't catch on to any patterns and
attempt to use false approval codes.

Another way is to insert a CRC check (or something similar) so that it is
harder to make a false code. One way is just to add up all the digits in
the number (and then add up those digits if greater than 9) and anything
that does not come out at 7 (or whatever) is illegal.

There is a slightly more complex version for validating credit card
numbers, but it's the same principle.

<http://www.beachnet.com/~hstiles/cardtype.html>

Hope that helps


Tim F

 

[Dirk Goldgar]

[snip]

strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))

[snip]

Not addressing your immediate problem, but I'm puzzled by what you're
intending to achieve by using the constant expression

(99999 - 10000 + 1)

which, of course, always evaluates to 90000. It seems to me that your
complete expression

(Int((99999 - 10000 + 1) * Rnd() +
10000))

comes out exactly the same as

Int((Rnd() * 90000)) + 10000

which I posted.

By the way, you don't need to execute the Randomize statement every time
you want to get a random number. Once per session is enough.

 

[TCD]

Yeah, I see that now. A co-worker just pulled it from an
Access book since it seemed a simple solution until we
tried to plug the date field in.

-----Original Message-----

[snip]
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))

[snip]

Not addressing your immediate problem, but I'm puzzled by what you're
intending to achieve by using the constant expression

(99999 - 10000 + 1)

which, of course, always evaluates to 90000. It seems to me that your
complete expression

(Int((99999 - 10000 + 1) * Rnd() +
10000))

comes out exactly the same as

Int((Rnd() * 90000)) + 10000

which I posted.

By the way, you don't need to execute the Randomize statement every time
you want to get a random number. Once per session is enough.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[TCD]

Evidently we must be screwing up elsewhere. We want to be
able to input the date manually as well. It seems we
should do this: strSQL = strSQL & "(Code, [Name], Date)".

What should be our line after this: strSQL = strSQL
& "(Code, [Name])"

Thanks for your help.

-----Original Message-----
'Name' is a reserved word in Access. These should normally be enclosed in
square brackets. Perhaps try:

strSQL = strSQL & "(Code, [Name])"
and:

strSQL = strSQL & ",""" & Me![Name] & """);"

HTH,
TC

Okay, maybe you can help me here then. I found a way to
do two fields with the primary key set as random. When I
add a third field though, it gives me a run time error on
the next to last line.

Private Sub cmdInsert_Click()

Dim db As DAO.Database
Dim strSQL As String

Randomize

Set db = CurrentDb

strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(Code, Name)"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ",""" & Me!Name & """);"



Debug.Print strSQL

db.Execute strSQL

db.Close

End Sub




rather
than a string, a
5-digit Do
you really need codes
are stored, and a
unique index on the
record generate in
which you'd be collisions. I
hope you're not

.

 

[TC]

Hi TCD

Can I suggest something? You already have a debug.print strSQL in your code.
Give us the output of that debug.print, & the exact code that you are
currently using, & I'm sure that we will find the problem.

HTH,
TC

Evidently we must be screwing up elsewhere. We want to be
able to input the date manually as well. It seems we
should do this: strSQL = strSQL & "(Code, [Name], Date)".

What should be our line after this: strSQL = strSQL
& "(Code, [Name])"

Thanks for your help.

-----Original Message-----
'Name' is a reserved word in Access. These should normally be enclosed in
square brackets. Perhaps try:

strSQL = strSQL & "(Code, [Name])"
and:

strSQL = strSQL & ",""" & Me![Name] & """);"

HTH,
TC

Okay, maybe you can help me here then. I found a way to
do two fields with the primary key set as random. When I
add a third field though, it gives me a run time error on
the next to last line.

Private Sub cmdInsert_Click()

Dim db As DAO.Database
Dim strSQL As String

Randomize

Set db = CurrentDb

strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(Code, Name)"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ",""" & Me!Name & """);"



Debug.Print strSQL

db.Execute strSQL

db.Close

End Sub





-----Original Message-----
message
Yes, what we are attempting is basically a five
character
ID, 10000 - 99999, that will appear to be random. That
way our staff doesn't catch on to any patterns and
attempt to use false approval codes.

Except for the non-reuse restriction, you could use a
VBA function like
this to return a pseudo-random approval code in the
range 10000 - 99999:

'----- start of code -----
Function NewApprovalCode() As Long

Dim lngCode As Long
Static blnRandomized As Boolean

If Not blnRandomized Then
Randomize
blnRandomized = True
End If

lngCode = Int((Rnd() * 90000)) + 10000

NewApprovalCode = lngCode

End Function

'----- end of code -----

I designed the routine to return a number value, rather
than a string,
but it could easily be modified to return the code as a
5-digit
character string. The modified lines would be just:

Function NewApprovalCode() As String

and

NewApprovalCode = Format(lngCode, "00000")

However, the non-reuse issue isn't quite so simple. Do
you really need
to check that no new approval code has already been
used? If so, you
must have a table somewhere in which the approval codes
are stored, and
thus you'll need to do a lookup in the function to
verify that the code
that was just generated hasn't been used already, and
loop until you
come up with one that hasn't. Or else, you could put a
unique index on
that field in the table, and let the attempt to save the
record generate
an error. But I don't know enough about the context in
which you'd be
doing this to give you advice as to the best way to do
it.

Note that random numbers in a range of only 90000
possibilities are
going to generate a substantial number of collisions. I
hope you're not
planning to generate many of these.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

.

 

[Marshall Barton]

Evidently we must be screwing up elsewhere. We want to be
able to input the date manually as well. It seems we
should do this: strSQL = strSQL & "(Code, [Name], Date)".

Date is a built-in FUNCTION, only VBA recognizes functions
without their associated parenthesis. Everywhere else
(control expressions, SQL, etc), you must use the
parenthesis:

strSQL = strSQL & "(Code, [Name], Date())"

Another way is to put the date value itself into the SQL:

strSQL = strSQL & "(Code, [Name], " & _
Format(Date, "\#\/m\/d\/yyyy\#" & ")"