Microsoft Access Random Number: Setting As The Default Value

Microsoft Access Random Number: Setting As The Default Value

How do you generate Access random number and make it also unique so it can act as your primary key?

The first point to make is you do in fact have a ‘Random’ option (via the ‘New Values’ property) when setting a field to an ‘AutoNumber’ data type but you cannot edit this value (should the need arise) and the sequence is completely, well random!access random numberI use my own random series but still have control should I need to edit the field and it also has some sequential or patterned level to follow for auditing purposes too keeping records tidy when being sorted.

Microsoft Access Random Number: Setting As The Default Value – The Steps

I use four Microsoft Access functions nested together and apply them to a ‘Text’ data type field in the ‘Default Value’ property. There are

  • Format(Value, FormatString)
  • Now()
  • Int(Value)
  • Rnd()
  1. Create a field called My_ID and set as a ‘Text’ data type with a field size of 20.

     

  2. In the properties under ‘Default Value’ I’m going to use the following four functions to generate unique and random reference. The expression is:

     

    =Format(Now(),”yyyymmddhhnnss”) & Int((5*Rnd())+1) & Int((5*Rnd())+1) & Int((5*Rnd())+1) & Int((5*Rnd())+1) & Int((5*Rnd())+1)

  3. Save and run the table.

Now the first 14 characters for my Access random number is a string format of the current date and time combined starting with the year, month, day, hour, minute and then seconds which in itself should be good enough but should you mess with the computers clock, you never know! Therefore, adding five extra digits ranging between 1 and 5 as integers (using the Int function – whole numbers) this will make harder to duplicate.

Of course you can change the number of digits and their ranges to reduce the likely hood of any further potential duplication but using the five in my example should be more than enough to handle a unique field as an Access random number.

This is one of my tips I share in the weekly tips sign up (which is free to subscribe too). Why not engage and connect up with me for personal Access database one-on-one coaching?

This entry was posted in MS Access, Tables and tagged , , , , . Bookmark the permalink.

One Response to Microsoft Access Random Number: Setting As The Default Value

  1. Ben Beitler says:

    Be careful when re-populating values in an existing table using an update query as this will not necessarily work due to your field being unique and the same random value is trying to update all records.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.