Generating a number

[UT227]

I need to generate a specific type of number for each record created. I'm hoping it can be done. For each new record, I need: two digit Alpha character followed by the last two digits of the current year, then a sequential 3 digit number. Example: IA17-001, IA17-002, IA17-003.....

[ssanfu]

I need: two digit Alpha character followed by the last two digits of the current year, then a sequential 3 digit number.

Will the 2 alpha characters always be "IA"?
If there are other letters to be used, does the sequential 3 digit number increment for regardless of the two letters or is it sequential by letter group?

Example:
IA17-001, IA17-002, WO17-003, IA17-004, WO-005, WO17-006

or

IA17-001, IA17-002, IA17-003
WO17-001, WO-002, WO17-003


I would use 3 fields to create this number: I would have a field for the 2 letters, a field for the 2 digit year and a field for the sequential number.



Take a look at Custom Autonumbers
http://baldyweb.com/CustomAutonumber.htm

[orange]

UT227,

Can you tell us a little about the rationale for this need?
I agree with Steve's response, but so often poster see an answer and it becomes a need.
I'm not suggesting you are doing this, but just checking.

Good luck.

[UT227]

Yes. The first 2 alpha characters will always be, "IA." This is more of a way to make it easier for the user. The number can be generated by hand. I just thought it would be easier if the program did it automatically. The last three numbers are independent of the first part. The IA indicates the department. The next two indicates the year. The last three are just sequential numbers to show how many records are generated by that department and in the year.

[orange]

I recommend you don't do that. Relational databases work very well with 1 fact in 1 field. I suggest you have
-a department Code
-a year field
-a number

You can display any combination on a form to simplify things for users

DeptCode & Year(yourdatefield) & YourNumber

If you use an autonumber for your number code, it will not necessarily be sequential. Autonumber are for uniqueness (not necessarily positive nor sequential). If an autonumber is used, and for some reason the record removed/deleted/canceled, that autonumber value is gone --hence loss of sequential numbering.

You can query your tables at any time using appropriate criteria and get a count of records.

Good luck with your project.

[ArviLaanemets]

At viewpoint of database design, having an autonumber is preferable, except when the number of records in table can rise above max for long integer, or when table contents are deleted and read in anew by some schedule. But sometimes are there other things to consider.

I have an IT Devices database, where tblDevices has Primary Key index in format CCYYYYMMDDnnn. The reason for such design is, that all devices are labeled with this code, and computers are named in domain using this code - so network admins have some information about device at glance.
CC - item group like CD for Computers: Desktop, CS for Computers: Server, NM for Network Devices: Manageable Switch etc.
YYYYMMDD - the date of registering/purchase. NB. This isn't sequential number - when some device is entered errousenly and then deleted, there will be a gap in numbering.
nnn - Max(nnn) of Devices of type CC, registered at YYYYMMDD, +1, preceeded with zeros

To generate this code:
a) tblDevices has a field for device group too. The Devices form is subform on unbound Main form, where the Device Group is selected in unbound combo box. Devices form is bound to this combo box, so for every new record Device Group is entered automatically.
b) When new record is created in Devices form (NB. All visible controls except navigation combo box on form are disabled usually), an invisible text box with current date is made visible - the user can leave it as is, or enter another date earlier as today, to register device afterwards. Also a invisible button to register a device is made visible.
c) On Devices form is hidden textbox, where new nnn is calculated, formula DMax(...) +1 is used.
d) When the button is clicked, the new DeviceID is written into appropriate control. The text box for date and the button are hidden again, and for all other visible controls editing is allowed (except for DeviceID).
e) After all info is entered, the user clicks a button <Save and Lock Record>

[UT227]

Wow. Thanks. That had me confused. I'm not quite that advanced. But, I'm learning. I'm going to practice on that and the others later. I have too many projects going right now. Looks like the number will have to generated by hand right now. Thanks for the advice.

[orange]

UT227,

I'm attaching an article on relational database principles by Don Ravey. It's a very good overview of the main things to be concerned with when starting with relational database. It's also an excellent refresher.

Good luck.