Question anding to year number

[kencpeck]

I have a table that has ID's in them with the format of YY-NUM. Every time the year changes the ID is to go back to 1. I was wondering if it's possible to have this field add 1 to the NUM and keep track of the year. My other option was going to have the year and the ID in two separate fields, But I'm not sure how I would get the ID to reset when the year changes.

Any help would be greatly appreciated.
-Ken

 

[pbaldy]

The usual method would be using DMax() on the number field with a criteria on the year. Presuming you have a date field, I wouldn't store the year separately; you can peel it out of the date field with the Year() function.

 

[arnelgp]

you need your a form bound to
your table to accomplish this.

you might even considering
putting the full 4 digit of year.

you can use BeforeInsert Event
of the Form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim ThisYear As Integer
Dim ThisValue As Variant
Dim TableYear As Variant
Dim Series As Variant

ThisYear = Val(Format(Year(Date()),"yy"))
ThisValue = DMax("ID", "yourTable")
If IsNull(ThisValue) Then
Me.ID = ThisYear & "-1"
Else
TableYear = CInt(Split(ThisValue,"-")(0))
Series = CLng(Split(ThisValue, "-")(1))+1
Me.ID = TableYear & "-" & Series
End If

End Sub

 

[plog]

You need to fix your database to store data properly. Each distinct piece of data needs to be stored distinctly. That means since you have 2 pieces of data in your YY-NUM field, it needs to be stored in 2 fields. One for the YY value, then one for the NUM value.

 

[arnelgp]

that means you will be
wasting 1 field for the whole
year with same data on it.

what happens to Normalization?
its repeating.

 

[plog]

Normalization is what I prescribed. Normalization doesn't mean eliminate all repeating data.

Tables can have tons of repeating data:

tblSales
SalesManID, SalesDate, SalesAmount
1, 1/1/2017, 17
2, 1/1/2017, 17
3, 1/1/2017, 16
1, 1/2/2017, 14
2, 1/2/2017, 17

Lots of those values repeat. That table is still normalized. Each discrete piece of data is in its place.

 

[pbaldy]

But why store the year when you have it in the date? Would you store:

SalesManID, SalesDate, SalesAmount, SalesYear
1, 1/1/2017, 17, 2017
2, 1/1/2017, 17, 2017
3, 1/1/2017, 16, 2017
1, 1/2/2017, 14, 2017
2, 1/2/2017, 17, 2017

 

[kencpeck]

you need your a form bound to
your table to accomplish this.

you might even considering
putting the full 4 digit of year.

you can use BeforeInsert Event
of the Form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim ThisYear As Integer
Dim ThisValue As Variant
Dim TableYear As Variant
Dim Series As Variant

ThisYear = Val(Format(Year(Date()),"yy"))
ThisValue = DMax("ID", "yourTable")
If IsNull(ThisValue) Then
Me.ID = ThisYear & "-1"
Else
TableYear = CInt(Split(ThisValue,"-")(0))
Series = CLng(Split(ThisValue, "-")(1))+1
Me.ID = TableYear & "-" & Series
End If

End Sub

Thank! I think it's working! Will this reset to 1 every year? ex: 18-001?

 

[plog]

But why store the year when you have it in the date? Would you store:

No. But that's a presumption you introduced. It's not clear if the original poster has a date field.

 

[pbaldy]

Point taken.

 

[arnelgp]

let me revised the proposed code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
	Dim ThisYear As String
	Dim ThisValue As Variant

	'* get the last digit of the year
	ThisYear = Format(Date(), "yy")
	'* get the latest ID from table
	'* where the Year is ThisYear
	ThisValue = DMax("ID", "yourTable", _
		"Left(ID, 2)=" & Chr(34) & ThisYear & Chr(34))

	If IsNull(ThisValue)
		'* did not find the Year
		'* then this is new year
		'* start series with one
		Me.ID = ThisYear & "-" & Format(1, "000")

	Else
		'* we did find it
		'* add one to the series
		Me.ID = ThisYear & "-" & _
			Format(Val(Split(ThisValue,"-")(1)) + 1, "000")
	End If
End Sub

 

[Pat Hartman]

I would Keep the year separate because it (and only it) is part of the natural key. That allows you to use an autonumber as the PK and a unique index on SaleYear and SeqNum. A unique index on SaleDate and SeqNum doesn't produce the same result.