new serial number for each year

[Guest]

hi,
is there a way to start new id no for each year
ie id no =2005-001 ,2005-002 or 2006-001, 2006-002 etc.
now all recored i have entred is showing numbers 1, 2, 3 so first i want to
change the format of the id numbers then i want it to start with new serial
automaticaly with the start of the year 2006.

 

[Rick Brandt]

hi,
is there a way to start new id no for each year
ie id no =2005-001 ,2005-002 or 2006-001, 2006-002 etc.
now all recored i have entred is showing numbers 1, 2, 3 so first i want
to
change the format of the id numbers then i want it to start with new
serial
automaticaly with the start of the year 2006.

In the BeforeUpdate event of the form used to enter records...

If IsNull(Me.ID) Then
Me.ID = Nz(DMax("ID", "TableName", "Year(RecordDate) = Year(Date())"),
0) + 1
End If

The above assumes that the table contains a field [RecordDate] that contains
the date-time of when the record was entered and that ID is a numeric field
with no default value.

 

[Ed Warren]

You might want to rethink why you really need to do this. You are trying to
combine two concepts: Year and ID in the same field. This will lead to
trouble in the future. In general your ID should contain no 'intelligence'
(e.g. just be a number with no meaning).

Ed Warren

 

[BruceM]

I have used something of the sort you need to number reports. I have some
more projects involving the same sort of technique, so I like to experiment
with this. In a situation where I had a similar question I received an
excellent reply from Allen Browne, with additional explanations and
suggestions by Tim Ferguson. Adapted to your situation it would go
something like the following in the form's Current event. The code assumes
that the field in which the number is to appear is called SequenceNum, and
that the table is tblYourTable. Note that SequenceNum in tblYourTable must
be a Text field. Substitute the actual names you are using, of course.

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "SequenceNum Like """ & Format(Date, "yyyy") & "-" &
"*"""
varResult = DMax("SequenceNum", "tblYourTable", strWhere)

If IsNull(varResult) Then
Me!SequenceNum = Format(Date, "yyyy") & "-001"
Else
Me!SequenceNum = Left(varResult, 5) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

This will affect only new records. If you need to renumber existing records
you will need another approach for those.

 

[BruceM]

I am going to respectfully disagree that a PK "should" contain a number of
no meaning. As long as the number is unique it ought to be suitable as a
PK. An account number, for instance, or an employee ID number, should work
well. Having said that, I would rather add a single PK field rather than
combine fields to assure a unique key, but there are some who disagree on
this. With me it's partly that I don't want to venture into that territory
uneless I need to.
I have used a system similar to the one I described to number reports that
must each have a unique number. Converting the year into a text string and
combining it with an incrementing number in a text field has worked well for
me. I used the field as my PK, although I have to say that I have not yet
used the PK in any relationships, nor am I likely to in that project, which
is essentially a flat database for recording report information.