Autonumber for each year

A

apprentice

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.
 
K

KARL DEWEY

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.
 
A

Allan Murphy

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
'
****************************************************************************
****
 
Top