Autonumber for each year

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
apprentice said:
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.
Click to expand...

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