Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Ravenna, Ohio, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    700,000 + Autonumber (Access 97)

    Is there a way to AutoNumber + 700,000? Please let me know how I would be able to do that if it is possible.

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

    Re: 700,000 + Autonumber (Access 97)

    What do you want to do?

    Do you want to add 700,000 to an AutoNumber field in a query? In that case, use an expression like [Fieldname]+700000 where Fieldname must be replaced by the actual name of your field.

    Or do you want to add 700,000 to the value of an AutoNumber field in the table itself? You can't do that with an AutoNumber field.

    Or do you want to start your AutoNumbers at 700,000? It's possible, but if you want this, perhaps an AutoNumber is not the right kind of field.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 700,000 + Autonumber (Access 97)

    To what purpose? Autonumbers are not intended to be data, merely unique identifiers. If you start trying to force them to be something else, you're better off using something else in the first place.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 700,000 + Autonumber (Access 97)

    OK guys, I have one for you then. Say I have an autonumber field. I was playing around with some code, ran it and the thing ran wild adding records. There was a problem with my do, loop statement. Anyhow, I have fixed that but now I want the numbers to start back at around 300 where they originally were. How do I make it say 300 instead of 461000........

    Thanks,
    Mark

  5. #5
    New Lounger
    Join Date
    Oct 2002
    Location
    Chattanooga, Tennessee, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 700,000 + Autonumber (Access 97)

    If you have deleted the offending records then all you need do is compact the db.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: 700,000 + Autonumber (Access 97)

    I think that works for Access 2000/2002 but it seems to me it doesn't always work for Access 97 - unfortunately I don't have it installed anywhere anymore so I can't test it.
    Wendell

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

    Re: 700,000 + Autonumber (Access 97)

    I seem to be in a contradictory mood today. I still use Access 97, and after compacting a database, autonumbering always resumes at (highest existing value)+1. (Gaps between existing values are not re-used, which is OK.)

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 700,000 + Autonumber (Access 97)

    Thanks Everyone,
    I am running AXP. I deleted the offending records and compacted the DB but I still get a number that is too high.

    Thanks,
    Mark

  9. #9
    New Lounger
    Join Date
    Oct 2002
    Location
    Chattanooga, Tennessee, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 700,000 + Autonumber (Access 97)

    This propbably means that there is some record that has a very high number.

    Anyway, Charlotte is right. Autonumber is not designed for human consumption. It is strictly for Access's use only to uniquely identify (address) a record. Don't try to use them for anything else, you'll be very dissapointed. What difference does it make what the autonumber is?

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 700,000 + Autonumber (Access 97)

    It was 2000 that didn't always work for. There was a bug corrected in SR-1. You could also run into wierdness in 97 if you were running NT and didn't have at least SP4.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 700,000 + Autonumber (Access 97)

    I feel like nitpicking <img src=/S/devil.gif border=0 alt=devil width=15 height=15>! I agree that if you care what values the autonumbers are then you are misusing the feature; however they can be useful for human consumption as long as it's made painfully clear that they're simply unique identifiers, no more, no less. Displaying and allowing the user to refer to an autonumber can be a convenient way to let a user quickly go to a particular record (e.g. to let him/her quickly copy corrections from a paper copy of a record to the database record).

    Kind of like null numeric values (which some people don't seem to approve of <img src=/S/grin.gif border=0 alt=grin width=15 height=15>) - they can be very useful but you need to be very careful with them.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 700,000 + Autonumber (Access 97)

    I usually let them select from something that is meaningful, like a name or customer code, etc., but I don't make that the primary key. The autonumber is tucked away out of sight and only Access knows what it is.
    Charlotte

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 700,000 + Autonumber (Access 97)

    DId I read that right? You're copying the table to tblTemp, deleting all the records in tblTemp and compacting, then deleting the original table? When were you planning to salvage any usable records from the original table?
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 700,000 + Autonumber (Access 97)

    Delete the realtionships with the existing table
    copy the table under a new name, tblTemp.
    Delete all the records in tblTemp.
    Compact the database.
    Do an append query selecting all the fields except the ID field from the original table and appending to the temp table.
    Delete the original table.
    Rename tblTemp to the name the original table had.
    Reconstruct the relationships with the new table.

    Don't do this until Wendell or Charlotte have confirmed that it will not destroy other things.
    David Grugeon
    Brisbane Australia

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 700,000 + Autonumber (Access 97)

    Oops. I have edited the post to say what I meant. Glad I thought an expert should check it. Thanks
    David Grugeon
    Brisbane Australia

Page 1 of 2 12 LastLast

Posting Permissions

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