Welcome to SPN

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

Sign Up Now!

How do I create an autonumber that also includes letter and year?

Discussion in 'Information Technology' started by Dedren, Nov 4, 2005.

  1. Dedren

    Dedren
    Expand Collapse
    Guest

    It is a case number of the format: F05-123
    -F stands for the type of case
    -05 is the year the case was opened
    -123 is just a number to tell them different.
    I want the entire alphanumeric serial to be autogenerated based on
    information entered in the database using a form.

    I have read some similar questions but nothing tackling something this
    complex.
     
  2. Loading...


  3. Sprinks

    Sprinks
    Expand Collapse
    Guest

    Dedren,

    The simplest way to implement this is to store the pieces in separate fields
    and concatenate them for display to your users.

    If you don't need to renumber starting with one at the beginning of each
    year, and you don't mind a missing case number if a case is deleted, you
    could use one numeric field for the case type and an autonumber primary key
    for the case number. Display the composite case number in an unbound
    textbox, and similarly build it as a calculated field in a query for use on
    your reports.

    Assuming that the text corresponding to the selected file type ID is in the
    2nd column, and that you have a DateOpened field that places the case in a
    particular year, the expression for the concatenated filenumber would be:

    =[YourComboBox].Column(1) & format([DateOpened],"yy") & Trim([CaseNumber])

    If you wish to renumber each year, you can use DMax to determine the highest
    casenumber for the current year, and then add one.

    Hope that helps.
    Sprinks

    "Dedren" wrote:

    > It is a case number of the format: F05-123
    > -F stands for the type of case
    > -05 is the year the case was opened
    > -123 is just a number to tell them different.
    > I want the entire alphanumeric serial to be autogenerated based on
    > information entered in the database using a form.
    >
    > I have read some similar questions but nothing tackling something this
    > complex.
     
  4. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Dedren wrote:
    > It is a case number of the format: F05-123
    > -F stands for the type of case
    > -05 is the year the case was opened
    > -123 is just a number to tell them different.
    > I want the entire alphanumeric serial to be autogenerated based on
    > information entered in the database using a form.
    >
    > I have read some similar questions but nothing tackling something this
    > complex.


    As noted, you use two fields and combine them when you display the
    results.

    I suggest you may not want to use Autonumber for that use. Autonumbers
    are designed to provide unique numbers. It in not designed to provide
    numbers in order and for a number of reasons may not do so. As a result
    using them in any application where the user sees the numbers is likely to
    end up with confusion.

    There are other ways of providing the numbers you want depending on the
    particual application.


    --
    Joseph Meehan

    Dia duit
     

Share This Page