setting a random autonumber to always be 6 digits long

[edisonxc]

I have a column in a table of employees where i want it to generate a random 6 digit number to serve as a password to be given out to the employee. I set the datatype to autonumber, the Field Size to LongInteger and the the NewValues to random. BUT... how do i limit the output to be a positive 6 digit number everytime??? I tried setting format to '000000' but that did not work.
help please.
thanks,
-mike

 

[Andrew Fergus]

Hi, I'm not sure how to limit the digits to 6 using the autonumber but there is a workaround using a new field, as follows :

Create a new field in your table for the password, called say "PW6", Long Integer, format 000000, 0 dp.

Create a new query, add the employee table, add the "PW6" field, Query -> Update Query, in the "Update To" field enter something like this :

IIf(Abs([rand])<100000,Abs([rand]),Right(Abs([rand]),6))

and set the criteria to : Is Null.

Save and Run.

This will populate the new field "PW6" with the last 6 digits of the random number. Whilst there is a low probability that two employess will have the same "PW6" value, it is possible. You could also use "Left" instead of "Right" or even a "Mid" function. You might also want to use "Left" for even numbers and "Right" for odd, or vice versa - really you can do anything you like.

Please note that I have used the variable name "rand" - you may want to use your own variable name.

I'm not sure what the maximum value of the random number is but if you knew it then you could express the absolute value of the random number as a proportion of the maximum possible random nubmer, multiply that proportion by 900k, truncate the number and then add 100k to give you a number with 6 digits.

HTH, Andrew.

 

[Andrew Fergus]

The formula for converting your random autonumber into a random number between 100,000 and 999,999 is :

=100000+Int(899999*(Abs([rand])/2147483647))

again using the [rand] variable.

Once again, this is to update the new field in your existing table via a query. You can autopopulate this field by using the query inside a macro when a new record is created in the employee table.

HTH, Andrew.