Autonumber by year

[snakbrat]

I have a project management db that I need to insert some type of auto numbering scheme on. I would like to have the numbering be tied to the year - starting with 2003. So the first record would be 2003.1, 2003-1 etc. (or anything similar). I know how to start #ing at 2003 but the next increment would then be 2004 - and that won't work. Maybe autonumbering is not the way to accomplish this. Any ideas?


Thanks in advance and love the slick forum

 

[ColinEssex]

There has been quite alot of posts on prefixing autonumbers with digits etc.

Have a search on these forums and see what you get. Post back if you don't get any luck

Col

 

[Sharad]

Auto Number

Interestingly enough I had similar issue. I got it resolved somehow and it is working fine. In this case ActionItemID is a Table with AutoNumber. Start the Number with 0 and it should return : 2003-1, 2003-2, etc. and in the following Years it should be return : yyyyy.nnn

'Add a record to the table at the Click of Update button
'Declare variables
Dim StrSql As String
Dim strQuote As String

'Update Action ID Number by +1
StrSql = "UPDATE ActionItemNumber SET ActionItemNumber.ActionItemID = [ActionItemID]+1"
DoCmd.RunSQL (StrSql)

StrSql = ""
StrSql = "Select ActionItemID from ActionItemNumber where 1"

Me!lstNum.RowSource = StrSql
Me!lstNum.Requery

'Display ActionReportNumber as yyyy-xx
txtActionReportNum.Value = Year(Date) & "-" & Me!lstNum.ItemData(0)

I hope this helps.