Autonumber random becomes scientific

[Eleanor of Aquitaine]

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.

 

[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]

 

[Eleanor of Aquitaine]

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,

 

[Eleanor of Aquitaine]

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.

 

[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]