I'm having a table that has a primary field as auto number. I want to add year to this field and have it reset after each year. Can anybody help me with this please?
Currently it's format as 0"8-"000 but I want it to be using a datetime field called DateofService and use the year mentioned there and create/append the autonumber
For example: DateOfService: 05/01/2007 then GC # 07-001 and if there are other records for that year it should increment it sequentially. If the year changes it should reset and start with the same (05/01/2008 08-001).
Table name: TBL_BCInformation
Fields: GC # (primary key) autonumber