Auto Numbers (without using AutoNumber)

[Don Marshall]

I am a new user
I have a database that requires the issuance of a "case" number. In order to work within replication, and multiple regions, each number's first digit identifies the region (there are eight). The "case" number consists of the region digit, a two digit year and a four digit sequencial number - and the number is stored in a single field. Therefore, someone would issue 2040001, 2040002, 2040003 for three cases and another region would issue 6040001, 6040002, and so forth
To keep track, I wrote a query (with an accompanying control button) that the user can press and it shows the most recent case number assigned (therefore, the next to be assigned is the next one in sequence)
Question: Does someone know of an expression where, instead of just providing the list of issued number, it would look for the last one issued ("where" would be first "digit=" and the control button would exist for each number span), add "1" and place the result into the correct field? It seems possible in my mind... I just don't know how I might do it

 

[Steve Schapel]

Don,

Many people would regard it as a mistake to put this case number in a
single field. I recognise that the business rules of the company
require the case number to be in a particular format, but in reality
this only applies from the point of view of the display of this number
on forms and reports, which is unrelated to the way it is stored in the
data tables. If you have the region number in one field, probably there
is already a date field somewhere from which the Year component is
available, and the suffix number as an incrementing integer in a
separate field, then the kind of thing you are trying to do will be
easier. When it comes to putting your case number on the forms and
reports, as a matter of course use the equivalent of...
CaseNumber: [RegionCode] & Format([YourDateField],"yy") &
Format([YourNumber],"0000")