Autonumber random becomes scientific

[Guest]

I'm using an Autonumber field to provide a unique field for my records. I
started with sequential numbers, which was working fine. Then I decided I
wanted the numbers to be generated randomly, so they are not at all
sequential. When I changed the format to "Random" it now generates a number
in scientific format (2B+03 for example), instead of just a random number.
I've tried changing the format to "General Number", but it didn't work.

Maybe I'm using autonumber wrong anyway. what I really would like is a four
digit, random number that is assigned automatically when I add a new record.
Is there a better way to do this than using Autonumber? I've read so many
posts on Autonumber and the main idea I'm getting is that most people use it
incorrectly. Maybe I'm one of them.

 

[Smartin]

I'm using an Autonumber field to provide a unique field for my records. I
started with sequential numbers, which was working fine. Then I decided I
wanted the numbers to be generated randomly, so they are not at all
sequential. When I changed the format to "Random" it now generates a number
in scientific format (2B+03 for example), instead of just a random number.
I've tried changing the format to "General Number", but it didn't work.

Maybe I'm using autonumber wrong anyway. what I really would like is a four
digit, random number that is assigned automatically when I add a new record.
Is there a better way to do this than using Autonumber? I've read so many
posts on Autonumber and the main idea I'm getting is that most people use it
incorrectly. Maybe I'm one of them.

Then you must have read how Autonumbers are not meant to be meaningful
to the user. It sounds like you want something meaningful, albeit random.

Off the top you would probably need a VBA function to create the key
which (a) generates the random number in the format you prefer and (b)
tests for uniqueness against the table before using it. Of course, the
more records you add, the more difficult (b) becomes, to the point of
being downright obnoxious after 9500 records or so have been committed.

Question for you: are you /certain/ you will never need more than 10,000
rows? Realize the laws of the universe dictate that as soon as you
commit to that scale, the application requirements will increase beyond
this bound.

 

[John Vinson]

I'm using an Autonumber field to provide a unique field for my records. I
started with sequential numbers, which was working fine. Then I decided I
wanted the numbers to be generated randomly, so they are not at all
sequential. When I changed the format to "Random" it now generates a number
in scientific format (2B+03 for example), instead of just a random number.
I've tried changing the format to "General Number", but it didn't work.

Maybe I'm using autonumber wrong anyway. what I really would like is a four
digit, random number that is assigned automatically when I add a new record.
Is there a better way to do this than using Autonumber? I've read so many
posts on Autonumber and the main idea I'm getting is that most people use it
incorrectly. Maybe I'm one of them.

The number is displaying in scientific notation because there's not
room in your textbox to write out a ten-digit negative number. Make
your textbox wider and you'll see -1412449823 or whatever it is.

You're correct: autonumbers are NOT suitable for what you want, and
cannot be coerced into doing what you want. See the other response for
some alternatives.


John W. Vinson[MVP]

 

[Guest]

In this case, we do have a finite number of records as we are gathering data
for a group of 600 students. Can you help me with a code for this? I'd
really appreciate it.

Thanks,

 

[Guest]

Okay, thanks John. I've been suspecting this for a while now. I've replied
to Smartin and I'm hoping he or someone could help me with the code required.

Thanks for responding and for the note about the textbox width.

 

[Smartin]

In this case, we do have a finite number of records as we are gathering data
for a group of 600 students. Can you help me with a code for this? I'd
really appreciate it.

Thanks,

Here's the first answer. It's a brute force technique but given a pool
of 600 ID's needed from 10000 possible it should perform well. There are
other ways, I'm sure.

Notice I included a break point of 10000 tries (guesses). If the
function tries 10000 times it will bail and return 0, indicating it has
not found a unique entry.

Insert the following in a standard module:

' ===== code begin
Public Function GenerateID() As Long
' return a unique number from 1 to 9999 that is not in test table
Dim Candidate As Long
Dim Tries As Long
Dim RS As DAO.Recordset
Dim SQL As String
Randomize
Do
Tries = Tries + 1
' generate a random number
Candidate = CLng(Rnd() * 10000)

' test for uniqueness
SQL = "SELECT * FROM TestTable WHERE ID = " & Candidate & ";"
Set RS = CurrentDb.OpenRecordset(SQL)
Loop Until RS.EOF = True Or Tries > 10000
If Tries > 10000 Then
GenerateID = 0 ' indicates failure
Else
GenerateID = Candidate ' return a successful candidate
End If
End Function
' ===== code end

Another technique that comes to mind is to build a table of all possible
values plus a column that indicates whether the value is "taken". You
could then construct a recordset of all the "untaken" items, and
randomly select a final result from that pool. This would ensure you are
always picking a random value the first time. The downside of this is
increased maintenance: You would need to update this table every time a
new ID is committed.

 

[Smartin]

Here's the first answer. It's a brute force technique but given a pool
of 600 ID's needed from 10000 possible it should perform well. There are
other ways, I'm sure.

Notice I included a break point of 10000 tries (guesses). If the
function tries 10000 times it will bail and return 0, indicating it has
not found a unique entry.

Insert the following in a standard module:

' ===== code begin
Public Function GenerateID() As Long
' return a unique number from 1 to 9999 that is not in test table
Dim Candidate As Long
Dim Tries As Long
Dim RS As DAO.Recordset
Dim SQL As String
Randomize
Do
Tries = Tries + 1
' generate a random number
Candidate = CLng(Rnd() * 10000)

' test for uniqueness
SQL = "SELECT * FROM TestTable WHERE ID = " & Candidate & ";"
Set RS = CurrentDb.OpenRecordset(SQL)
Loop Until RS.EOF = True Or Tries > 10000
If Tries > 10000 Then
GenerateID = 0 ' indicates failure
Else
GenerateID = Candidate ' return a successful candidate
End If
End Function
' ===== code end

Another technique that comes to mind is to build a table of all possible
values plus a column that indicates whether the value is "taken". You
could then construct a recordset of all the "untaken" items, and
randomly select a final result from that pool. This would ensure you are
always picking a random value the first time. The downside of this is
increased maintenance: You would need to update this table every time a
new ID is committed.

Sorry, I forgot a cardinal rule.

Place this statement before _End Function_ :
Set RS = Nothing


FYI, My testing show it will seldom if ever take more than 4 random
guesses to pick 600 random numbers from a pool of 10000. However, as the
demand increases, the number of random tries increases exponentially to
about 1000 tries needed when 9980 ID's are already taken. If your demand
stays small you shouldn't have any problem with the brute force method,
however inelegant it may be (^:

 

[John Vinson]

In this case, we do have a finite number of records as we are gathering data
for a group of 600 students. Can you help me with a code for this? I'd
really appreciate it.

Just as an alternative to Smartin's suggestion, you could try a
somewhat different solution. Create a table named Num with 9999 rows,
with one long integer field N, with values 1 through 9999 (you can
create it quickly in Excel with Fill... Sequence).

If your table is named MyTable with the (random) ID named ID, you can
create a Query:

INSERT INTO MyTable(ID)
SELECT TOP 1 N
FROM NUM
LEFT JOIN MyTable
ON MyTable.ID = Num.N
WHERE ID IS NULL
ORDER BY Rnd([N]);

This "frustrated outer join" query will select only those values of N
which have *not* been inserted into MyTable yet; sort them into random
order; and pick the first one. Run this query from your form to create
a new record.

John W. Vinson[MVP]