Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoNumber Field (Access 97)

    I created a database that assigns a unique number to each entry (AutoNumber field)
    field size = Long Integer
    new values = Increment
    format =

  2. #2
    New Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber Field (Access 97)

    I have just created a table with such a primary key, entered some rows and exported it to another Access 2000 mdb. I opened the table. my data was there (98001 to 98004) and I added another couple of rows (98005, 98006) No problems! Were you trying to export to something other than an Access mdb?

    Dr Godfrey Nicholson
    Ofek Technologies Ltd
    Auckland, NZ the hub of the Pacific

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: AutoNumber Field (Access 97)

    It didn't truncate the 98, the 98 is part of the format and not the data. Access merely exported the autonumber itself.

    So instead of exporting the autonumber field, you need to export an expression containing the format function. Create a query containing all the fields from your table, but instead of the autonumber field, use an expression containing your format function, something like:

    FormattedID: Format( ID, "98"000)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoNumber Field (Access 97)

    The problem with "format" is that it is just that - it defines the format the data will be displayed in. It doesn't in any way modify the data contained in the field. If you export an Access 2000 (or any other version) table to another Access DB of the same version, it will keep all its properties including the formats which have been setup. Try removing the format and then looking at the data. I'm certain that all you'll have is 1, 2, 3, 4, 5, 6.

    If you really want the values to start at 98001, you could append a record to the table with the value 98000 in the AutoNumbered field before you start, the numbers will then increment from that point, but I can't think of another way using AutoNumber. If the field was changed to a long value, it could be calculated in code - that would allow all kinds of custom auto-numbering.

    Simon

  5. #5
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber Field (Access 97)

    Yes I'm trying to export to DBase III format

  6. #6
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber Field (Access 97)

    Thank you for your help, what I ended up doing was exporting to excel used =980&A1 to make the correct number (adding or deleting a zero) then I imported the 250 records as a table then appended them all to a new table with the same design as the other except I left out any formatting in the auto number field, it workked,

    Thanks

    Ray

Posting Permissions

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