Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Aug 2005
    London/Kingston, Surrey, United Kingdom
    Thanked 0 Times in 0 Posts

    Data Type in access - min 6 digits

    Hi all,

    I am creating a table on access database (2010), I have made AssetID as the primary ID but would like to set the following conditions:
    It has to have a six digit number and display the zeros in front: i.e. 000001, 000002 0000003 and so on, if the user enters 5 digit number , the system should notify that they have to enter six digits. If someone could show how to do this I would really appreciate it.

    Many thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 Posts
    One solution is to use the Len() function in VBA to check the number of digits entered and to execute the code after the data has been entered into a form (you can't really do that at the table level). One it has been entered into a table, you can use the Format properties of the field to always display it as a certain number of digits. That of course presumes that you are saving the value in a numeric field, not a text field. If you store the primary ID as a text field, then the formating issue goes away, but you have other issues related to sorting and such that can trip you up, and you have the issue of making sure the data entered is numeric.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts