Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

In access, is it possible to autonumber with a prefix? ie ABC0001

Discussion in 'Information Technology' started by Coz, Jul 28, 2006.

  1. Coz

    Coz
    Expand Collapse
    Guest

    I use an incremented reference, always starting with the same three letters,
    can autonumber be set so these letters are always prefixing the autonumber
    (as opposed to the user having to check last ref, add one and type it in ???
     
  2. Loading...


  3. Rick B

    Rick B
    Expand Collapse
    Guest

    If the first three letters are ALWAYS the same, then don't worry about it.
    Just store the numbers. In your reports, forms, and queries, you can use
    code to add on the prefix, but why store it if it will always be the same?


    --
    Rick B



    "Coz" <Coz@discussions.microsoft.com> wrote in message
    news:F307BF1C-F370-4DFD-A7FF-474B27FB5142@microsoft.com...
    >I use an incremented reference, always starting with the same three
    >letters,
    > can autonumber be set so these letters are always prefixing the autonumber
    > (as opposed to the user having to check last ref, add one and type it in
    > ???
     
  4. Al Camp

    Al Camp
    Expand Collapse
    Guest

    Coz,
    No, the autonumber type is a long integer. ABC0001 would be a text field. Use an
    autonumber to generate the 1 or 2, or 3, etc... and then append the ABC and the
    appropriate number of zeros to your autonumber in another calculated text control.

    This will get a bit messy though... as the length of the autonumber will grow over
    time, and the correct number of 0's to be cocatenated to it will vary.
    Also, "pad" enough 0's to cover larger numbers. What happens after ABC9999 occurs?

    Also, that concatenated string does not have to be saved to a field. You can always
    calculate (just display) it on the fly in any form or report or query.
    Using my format of ABC-132, a calculated text control on your form with...
    = "ABC-" & [YourAutoFieldName]
    will always display the correct value/format you want.

    I'd suggest using ABC-1 or ABC-3145 type format, then the "ABC-" can be appended
    directly to the value of the auto field.

    --
    hth
    Al Camp
    Candia Computer Consulting - Candia NH
    http://home.comcast.net/~cccsolutions


    "Coz" <Coz@discussions.microsoft.com> wrote in message
    news:F307BF1C-F370-4DFD-A7FF-474B27FB5142@microsoft.com...
    >I use an incremented reference, always starting with the same three letters,
    > can autonumber be set so these letters are always prefixing the autonumber
    > (as opposed to the user having to check last ref, add one and type it in ???
     
  5. Coz

    Coz
    Expand Collapse
    Guest

    Re: In access, is it possible to autonumber with a prefix? ie ABC0

    Al

    I tried that - created an new autonumber field on the table (called New Ref)
    then entered = "ABC" & [New Ref] into the form - it displayed the ABC, but
    didn't add in the autonumber (BTW the form is set to Data Entry = Yes and New
    Ref is a brand new field)

    MTIA

    "Al Camp" wrote:

    > Coz,
    > No, the autonumber type is a long integer. ABC0001 would be a text field. Use an
    > autonumber to generate the 1 or 2, or 3, etc... and then append the ABC and the
    > appropriate number of zeros to your autonumber in another calculated text control.
    >
    > This will get a bit messy though... as the length of the autonumber will grow over
    > time, and the correct number of 0's to be cocatenated to it will vary.
    > Also, "pad" enough 0's to cover larger numbers. What happens after ABC9999 occurs?
    >
    > Also, that concatenated string does not have to be saved to a field. You can always
    > calculate (just display) it on the fly in any form or report or query.
    > Using my format of ABC-132, a calculated text control on your form with...
    > = "ABC-" & [YourAutoFieldName]
    > will always display the correct value/format you want.
    >
    > I'd suggest using ABC-1 or ABC-3145 type format, then the "ABC-" can be appended
    > directly to the value of the auto field.
    >
    > --
    > hth
    > Al Camp
    > Candia Computer Consulting - Candia NH
    > http://home.comcast.net/~cccsolutions
    >
    >
    > "Coz" <Coz@discussions.microsoft.com> wrote in message
    > news:F307BF1C-F370-4DFD-A7FF-474B27FB5142@microsoft.com...
    > >I use an incremented reference, always starting with the same three letters,
    > > can autonumber be set so these letters are always prefixing the autonumber
    > > (as opposed to the user having to check last ref, add one and type it in ???

    >
    >
    >
     
  6. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    Re: In access, is it possible to autonumber with a prefix? ie ABC0

    "Coz" <Coz@discussions.microsoft.com> wrote in message
    news:8CF3759F-F4EB-4BFF-A724-87B6BE777454@microsoft.com...
    > Al
    >
    > I tried that - created an new autonumber field on the table (called New
    > Ref)
    > then entered = "ABC" & [New Ref] into the form - it displayed the ABC, but
    > didn't add in the autonumber (BTW the form is set to Data Entry = Yes and
    > New
    > Ref is a brand new field)


    The Autonumber value won't be set until the Record is written, so you would
    not see it in a Data Entry form. It's not good practice to display
    AutoNumber Fields, in any case. They are not necessarily the "monotonically
    increasing" values that many expect -- they are intended to be _unique_, and
    used for internal purposes in the database (as "surrogate keys", linking
    related tables, for example).

    There have been quite a lot of postings of code to generate the
    monotonically increasing numbers, with no gaps in the sequence, that "green
    eyeshade accounting types" and others expect. Most of them use the DMAX
    domain aggregate function. Perhaps Googling the archives at
    http://groups.google.com using "sequential" and "DMAX" will turn up a useful
    solution.

    Larry Linson
    Microsoft Access MVP
     

Share This Page