Welcome to SPN

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

Sign Up Now!

Autonumber for each year

Discussion in 'Information Technology' started by apprentice, Nov 5, 2005.

  1. apprentice

    apprentice
    Expand Collapse
    Guest

    My database controls the contrating processes performed each year. I had an
    autonumber as the field identiying each process; but it's going to grow out
    of control and that number is later used for the contract awarded.

    How can I set an "autonumber" field that resets for each year.
     
  2. Loading...


  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    apprentice wrote:
    > My database controls the contrating processes performed each year. I
    > had an autonumber as the field identiying each process; but it's
    > going to grow out of control and that number is later used for the
    > contract awarded.
    >
    > How can I set an "autonumber" field that resets for each year.


    Well it can be done, but autonumber is really not the best tool to use.
    Autonumber is designed to provide a set of unique numbers. It is not
    designed to provide 100% consecutive numbers. Any of a number of things can
    cause it to do the unexpected. For example it can start providing negative
    numbers.

    Anytime you are using the number in such a way that real people will be
    seeing the numbers it is best to roll your own rather than use the auto
    number.

    I don't have it handy, but if you search the existing questions, I
    believe you will find a number of techniques of providing unique incremented
    numbers. I would guess it would not be too difficult to trigger a routine
    to rest the numbering each year.


    --
    Joseph Meehan

    Dia duit
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    All kinds of reason not to use autonumber for this purpose.

    Best to use unbound textbox on your entry form to fill in the next number.

    Use an IIF statement to check if MAX number year is the same as current
    year. If it is the same then increment by one. If different build new
    number based on the current year concatenated with “000001†or how ever many
    digits you need.

    Use the unbound textbox for the next record.


    "apprentice" wrote:

    > My database controls the contrating processes performed each year. I had an
    > autonumber as the field identiying each process; but it's going to grow out
    > of control and that number is later used for the contract awarded.
    >
    > How can I set an "autonumber" field that resets for each year.
    >
     
  5. Allan Murphy

    Allan Murphy
    Expand Collapse
    Guest

    I use the following functions that I created it may not be efficient but it
    does the job.

    Forms!frm_case_add!create_date is a field that is automatically set to the
    current date value when the user adds a record and this date is used to
    generate as case number.in the format YYYYMM/autonumber.


    Function case_number() As String

    ' Find the last reference and increment it by 1 for the new record.
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim temp_month As String
    Dim strcase As String
    Dim cases_number As String
    Dim nextfolio As Integer
    Dim temp_test As String
    Dim final_number As String


    If Month(Forms!frm_case_add!create_date) < 10 Then
    temp_month = "0" & Month(Forms!frm_case_add!create_date)
    Else
    temp_month = Month(Forms!frm_case_add!create_date)
    End If

    strcase = Year(Forms!frm_case_add!create_date) & temp_month


    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT clng(mid$(case_num,8,4)) as Folio FROM
    tbl_case_number " & _
    "WHERE (((Mid$([case_num], 1, 6)) = strcase_test())) ORDER BY
    clng(Mid$(case_num,1,6)) DESC;")


    ' If this is the first record for this year, then create an initial
    number
    If rst.BOF Then
    case_number = strcase & "/0001"
    rst.Close
    Exit Function
    End If

    rst.MoveFirst

    ' increase the job number by 1
    nextfolio = rst!folio + 1


    ' if the following nested IF STATEMENT was not included the second record
    created
    ' would be 00/2. But when sorted 00/22 would appear before 00/8
    ' because the alert is a string.

    ' Using these nested IF STATEMENTS the case_number is padded to four
    digits
    ' for the number depending on the value of the next number. This will
    remove
    ' the anamoly of trying to sort a string when the number portion is
    involved.
    ' the alert_number should be in the range of 001 to 999

    If nextfolio < 10 Then
    cases_number = "000" & CStr(nextfolio) ' pad with 3 zeroes

    ElseIf nextfolio < 100 Then
    cases_number = "00" & CStr(nextfolio) ' pad with 2 zeroes

    ElseIf nextfolio < 1000 Then
    cases_number = "0" & CStr(nextfolio) ' pad with 1 zeroes
    ' no padding required

    Else
    cases_number = CStr(nextfolio)

    End If

    rst.Close


    case_number = strcase & "/" & cases_number


    End Function

    '
    ****************************************************************************
    ****
    ' Format the current year and month into a standard format e.g. year 2005
    month 8 to be shown as 200508 not 20058
    Function strcase_test()
    Dim temp_month As String
    Dim case_date As Date

    case_date = Forms!frm_case_add!create_date
    If Month(case_date) < 10 Then
    temp_month = "0" & Month(case_date)
    Else
    temp_month = Month(case_date)
    End If

    strcase_test = Year(case_date) & temp_month

    End Function
    '
    ****************************************************************************
    ****

    --
    Allan Murphy
    Phone: 0403 955 223
    Email: allanmurphy@unwired.com.au
    "apprentice" <apprentice@discussions.microsoft.com> wrote in message
    news:5076766F-540B-4DC5-93CF-EF71B97536A4@microsoft.com...
    > My database controls the contrating processes performed each year. I had

    an
    > autonumber as the field identiying each process; but it's going to grow

    out
    > of control and that number is later used for the contract awarded.
    >
    > How can I set an "autonumber" field that resets for each year.
    >
     

Share This Page