Create project serial number

[chrisg101]

I have a job table where I need to have a project number field update automatically on a form.
The format is "03-023" where '03' is the year (can be sourced from the creation date). And '023' is the current project number for that year. I would like the project number to automatically increment until the next year ticks over in which I'd then like it to start back at '000' again. ie. first job next year would be '04-001'.

Does this sound possible. I figure it would be easier to split the field into two (fine by me). I can get the year from a start date field. I would assume I could add an expression that added one to the last value of project number.

Any solutions would be much appreciated. I have some experience in Access, but if it involves VB script please walk me through it.
Cheers, Chris.

 

[Darrylles]

Hi,

This is a general idea of how I would do this - not a blow by blow descriprion.

I would use a new small table for this:

tblProjKey
Proj_Year Text(2)
Proj_Num Text(3)

This table will only ever hold one record: i.e. the last used project number.

Give the record a base value (e.g. last project was '03-023', so Proj_year = "03", Proj_Num = "023".

I've made the fields text so that the leading zeroes would be easier to manipulate.

When you need a new project number, read the record, ALWAYS check that the Proj_Year is the current year, if it is - add 1 to Proj_Num, concatenate the strings and you've got your next project number.
Change the Proj_Num in the record and save it back to the table.

If Proj_Year is not current, then you've obviously recently entered 2004, change Proj_Year to '04', change Proj_Num to '001', concatenate them into your Project Number - save the record.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk