none
Setting the starting Number in a Auto Number field.

    Question

  • Hi All,

    I am using Access 2003, I have a table with a field called Order Number. I have set this field to be "AutoNumber".

    My question is, Can I tell Access which number to start at? I.e 100200 Rather then starting at 1.

     

    Thanks in Advance.

    Adam.

    Monday, June 28, 2010 4:54 AM

Answers

  • Hi Adam,
     
    Not at design-time. The easiest way is to write an insert query to add a record where the autonumber = 123 (or whatever). That way the next record will be 124.

    Hi All,

    I am using Access 2003, I have a table with a field called Order Number. I have set this field to be "AutoNumber".

    My question is, Can I tell Access which number to start at? I.e 100200 Rather then starting at 1.

     

    Thanks in Advance.

    Adam.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    • Marked as answer by adam_syd Monday, June 28, 2010 7:18 AM
    Monday, June 28, 2010 6:28 AM

All replies

  • Hi Adam,
     
    Not at design-time. The easiest way is to write an insert query to add a record where the autonumber = 123 (or whatever). That way the next record will be 124.

    Hi All,

    I am using Access 2003, I have a table with a field called Order Number. I have set this field to be "AutoNumber".

    My question is, Can I tell Access which number to start at? I.e 100200 Rather then starting at 1.

     

    Thanks in Advance.

    Adam.


    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    • Marked as answer by adam_syd Monday, June 28, 2010 7:18 AM
    Monday, June 28, 2010 6:28 AM
  • Graham,

    You certainly know your stuff...Worked a treat!.

     

    Once again, Thank you.

     

    Adam.

    Monday, June 28, 2010 7:18 AM
  • Adam

    Just in case you want another way to do it, you can run a query to alter the field. Open a new query and switch to SQL view.
    ALTER TABLE AutoNumTest
    ALTER COLUMN MyPK COUNTER(100200,1)


    Bill Mosca, MS Access MVP
    http://www.thatlldoit.com
    http://mvp.support.microsoft.com/profile/Bill.Mosca
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    <adam_syd> wrote in message news:78099ceb-4a51-45de-9da9-b442e8073a1d@communitybridge.codeplex.com...

    Hi All,

    I am using Access 2003, I have a table with a field called Order Number. I have set this field to be "AutoNumber".

    My question is, Can I tell Access which number to start at? I.e 100200 Rather then starting at 1.



    Thanks in Advance.

    Adam.


    Bill Mosca www.ThatllDoIT.com
    Monday, June 28, 2010 4:02 PM
  • Graham's suggestions will certainly work, but you should be aware that most developers avoid exposing Autonumbers to user view. The only guarantee with an autonumber is that it will be unique: they will always develop gaps. If you delete a record, that autonumber gets used up and will never be used again; even if you hit <ESC> a second or two after starting a new record, you'll also get a gap!

    If gaps aren't important to you, fine... but typically auditors get very antsy if records about money have apparently "missing" records. You may want to consider instead using a Long Integer field with a "Custom Counter" - a quick Bing or Google search will find tons of references for how to create one.

     


    John W. Vinson/MVP
    Monday, June 28, 2010 5:12 PM
  • Wow Bill, I didn't think to do that. I've recorded that now for future use.
     

    Adam

    Just in case you want another way to do it, you can run a query to alter the field. Open a new query and switch to SQL view.
    ALTER TABLE AutoNumTest
    ALTER COLUMN MyPK COUNTER(100200,1)


    Bill Mosca, MS Access MVP
    http://www.thatlldoit.com
    http://mvp.support.microsoft.com/profile/Bill.Moscahttp://tech.groups.yahoo.com/group/MS_Access_Professional s

    <adam_syd> wrote in message news:78099ceb-4a51-45de-9da9-b442e8073a1d@communitybridge.codeplex.com...

    Hi All,

    I am using Access 2003, I have a table with a field called Order Number. I have set this field to be "AutoNumber".

    My question is, Can I tell Access which number to start at? I.e 100200 Rather then starting at 1.



    Thanks in Advance.

    Adam.


    Bill Mosca www.ThatllDoIT.com

    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    Monday, June 28, 2010 10:50 PM
  • I'm glad you found it useful, Graham. Even us old dogs learn new tricks in forums, huh?

    If you use ADO or SQL Server syntax change COUNTER to IDENTITY.


    Bill Mosca, MS Access MVP
    http://www.thatlldoit.com
    http://mvp.support.microsoft.com/profile/Bill.Mosca
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    <Graham R Seach [MVP]> wrote in message news:3c02b668-d5a7-4c98-bbfa-8be113b2c328@communitybridge.codeplex.com...

    Wow Bill, I didn't think to do that. I've recorded that now for future use.

     "Bill Mosca [MVP]" wrote in message news:f0a55c4a-9f3f-4450-946f-f30a5e665d41@communitybridge.codeplex .com...
     Adam

     Just in case you want another way to do it, you can run a query to alter the field. Open a new query and switch to SQL view.
     ALTER TABLE AutoNumTest
     ALTER COLUMN MyPK COUNTER(100200,1)


    ------------------------------------------------------------------------------

     Bill Mosca, MS Access MVP
     http://www.thatlldoit.com

    http://mvp.support.microsoft.com/profile/Bill.Moscahttp://tech.groups.yahoo.com/group/MS_Access_Professional s

     <adam_syd> wrote in message news:78099ceb-4a51-45de-9da9-b442e8073a1d@communitybridge.codeplex.com...

       Hi All,

       I am using Access 2003, I have a table with a field called Order Number. I have set this field to be "AutoNumber".

       My question is, Can I tell Access which number to start at? I.e 100200 Rather then starting at 1.



       Thanks in Advance.

       Adam.


    ------------------------------------------------------------------------------
     Bill Mosca www.ThatllDoIT.com

    --------------------------------------------------------------------------------
    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia


    Bill Mosca www.ThatllDoIT.com
    Tuesday, June 29, 2010 2:27 PM