Validation rule for access

Microsoft Access 2003 eng (full product)
July 23, 2009 at 14:37:57
Specs: Windows Vista
I am working on a Microsoft Access project and need a validation rule for the job number.

I need it in the form 09-140

where the 09 represents the current year, and the 140 an incremental number which starts from 1 and automatically increases by 1 with each addition of a new job.

I would like the rule to autofill as I enter information for a new job, and for the 09 to automatically change to 10 when we go into the year 2010 etc.

For example:

For the first 3 jobs this year the job number would auto-fill:


and then if there was not a job until 2010, the next job number would be:


Is this possible?

Thanks in advance,


See More: Validation rule for access

Report •

July 27, 2009 at 17:48:23
Simple. You create two fieldes in the data base. One for the number using Access Auto data type which increments by one every time a new record is created. You also have a field that records the date the record is created.

When you come to display it you concentate just the year from the date and the data from the Auto fill field.

In VB his would be:

JobNo = DatePart("yyyy", Date) & "-" & Autofield

The Datepart function produces a four digit year. You should be able to work out how to reduce that to a two gigit year although I would stick with four digit years. It was two digit years that got us into the Y2K problem.


Report •
Related Solutions

Ask Question