Expert: Scottgem - 2/2/2005

Question
Hi All,

I'm designing this database, in which, I have to give each record a unique random (or sequance) number of 5 digits, giving that the first digit must be retrived from another field.

Please Help, I need this ASAP!

Any help will be very much appreciated!

Thanks,
CS.  

Answer
The expression:

=Int((100000 * Rnd) + 1)

Will generate a random number, usually of 5 digits. In my testing, It sometimes came up with less, though. So you can use this function to insure that its 5 digits

Public Function Random5() As Long
Do While Random5 < 10000
Random5 = Int((100000 * Rnd) + 1)
Loop

End Function

I just reread your request. If you are saying that the first of the 5 must be gotten from another field so that if the other field contains a 3 then the number should be 31234, then you need to change the function a bit.

Public Function Random5(intFirst As Long) As Long
Do While Random5 < 1000
Random5 = Int((10000 * Rnd) + 1)
Loop
Random5 = intFirst * 10000 + Random5

End Function

To retrieve the number use:
=Random5([fieldname])



HTH
Scott<>