Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limiting the number of digits in Autonumber field (Access 2000)

    Can you restrict the number of digits in an autonumber field? Random or increment doesn't matter. I need to limit the number of places to 5.
    Doable in table design or through code in a form?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limiting the number of digits in Autonumber field (Access 2000)

    You can set a validation rule on the table as a whole (not on the AutoNumber field by itself).
    Open the table in design view.
    Activate the Properties window.
    Say that the AutoNumber field is named ID. Enter the following expression in the Validation Rule property:

    [ID]<100000

    and enter a helpful text in the Validation Text property:

    You can't save this record because the AutoNumber field exceeds 5 digits.

    Users won't be able to do anything but cancel the record, because they can't edit the AutoNumber field.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting the number of digits in Autonumber field (Access 2000)

    So, they'd just have to try repeatedly until the system spat up a number of the right length? I must be misunderstanding.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limiting the number of digits in Autonumber field (Access 2000)

    If the AutoNumber field is set to Increment, the user won't be able to enter new records any more when it has reached 99,999, unless compacting the database resets the next AutoNumber to something below that. If the AutoNumber field is set to Random, users will have to keep on trying until the system comes up with a number below 100,000. By the way, did you want to have a number of exactly five digits? If so, the Validation Rule should be [ID] Between 10000 And 99999.

    But perhaps an AutoNumber field is not the ideal field type if you want to set this kind of limit.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting the number of digits in Autonumber field (Access 2000)

    But perhaps an AutoNumber field is not the ideal field type if you want to set this kind of limit.

    Well, I'm not wild about it either - someone will be cursing ME years from now! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Hopeful I'll be far away <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Thanks

Posting Permissions

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