Access programming help

[tedward]

[FONT=&quot]Example 1:[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]2011-1 [/FONT]
[FONT=&quot]....... [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]2011-3893 etc.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Currently I have an Access form which produces a new unique number to identify each new record created. To do this I use the unique ID autonumber from a table to identify the new records. I would like to change from this simple number to the the above format per example 1. The four digits to the left of the hyphen would always be the current year and digits to the right of the hyphen would be the unique auto incrementing numbers such as from my table. I need the year to auto increment by 1 each September 30th (new business year) and I need the numbers to the right to auto reset to 1 to start uniquely identifying records again for the new incremented year. As each record is closed I need the number to be written as a single entity in the new format to my database. How might this be accomplished?
[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Any help will be much appreciated. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Example 2: After September 30th.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]2012-1 [/FONT]
[FONT=&quot]....... [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]2012-447 etc.[/FONT]

 

[Steve R.]

First, your auto-number field should be independent of any formatting and should not be used in the manner you are requesting. See the sample code below for incrementing project numbers on a yearly basis and adapt to meet your criteria.

Note, that there are only four (right-hand) digits assigned to the project number meaning that the a project number cannot exceed 1000. This codes has an arbitrary 997 limit. A return of 9999 simply identifies that the number of projects has been exceeded. Subsequent code in the form takes care of that.

Function NewProjectnum()
    Dim lonCurrentYear As Long
    Dim lonLastnum As Long
    Dim lonTestYear As Long
    Dim intCaseSelected As Integer
    intCaseSelected = 1
    lonCurrentYear = Year(Date)
    lonLastnum = DMax("Projectnum", "ConsistencyMain")
    lonTestYear = Val(Left(lonLastnum, 4))   
    If Val(Right(lonLastnum, 4)) >= 997 Then intCaseSelected = 2 'Rem Test for too many project numbers
    Select Case intCaseSelected
        Case 1 'Results OK
            If lonTestYear = lonCurrentYear Then NewProjectnum = lonLastnum + 1 'Rem increment the project number for current year
            If lonTestYear <> lonCurrentYear Then NewProjectnum = Val(Str(lonCurrentYear) + "0001") 'Rem increment the project number for the start of a new year
        Case 2 'Too many projects
            NewProjectnum = 9999
        End Select
End Function

PS: The code was revised. What I originally posted was many years old. While it worked flawlessly, it was inefficient.