By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,978 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,978 IT Pros & Developers. It's quick & easy.

A table with Auto number generation field

P: 2
Hi all,

I’m trying to create a form with 15 fields that generates an auto number in one of those 15 fields. The auto generated number which should display like this – “081-2008-00001” “081-2008-00002” “081-2008-00003” and so on.
The first 3 characters of those numbers are pulled in from one of the field, namely “Building #”. The second set of 4 digit numbers are nothing but the current year which gets pulled from another field namely “Current Date” in the mm/dd/yyyy format (I need to pull only the last 4 digits of the date which is the year).
The Last 5 digits are auto generated numbers assigned by the system.
How do get this logic working for me?
Also, there are several other records which may have different building numbers (for ex. 41, 43, 50, 55, 75, 78, and so on). The last 5 digit auto generated numbers could be consecutive but the first 3 digits should be pulled from the “building #” field and the next 4 from the “current date” field.

Please help!

Thank you,

Irving Guy
Apr 10 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
Hi. The most useful way to generate a representation of a compound key (one that combines a number of key fields together to form a unique combined ID) is to create a query with all the fields from the table, then add a calculated field to the query. The calculated field can show the fields you mention combined together in the format you require.

The calculated field would be along the lines of:
Expand|Select|Wrap|Line Numbers
  1. CompoundKey: Format([Building#], "000") & "-" & Year([Current Date]) & "-" & Format([autonumber field], "00000")
On your form you then can add the new CompoundKey field, setting its properties to Enabled=No and Locked=Yes to prevent users from trying to change its (non-changeable) value.

I am assuming that you already have an autonumber field in your table. If you don't, you can create a numeric field of type Long and increment that, setting its default value as:
Expand|Select|Wrap|Line Numbers
  1. Nz(DMax("[long field]", "[your table name]"), 0) + 1
Autonumber fields have some properties that are considered unsuitable for use where the number is supposed to mean something to those who might see it. For example, autonumbers end up not entirely consecutive (if, for instance, a record addition is cancelled the autonumber is still incremented and the number that was generated is skipped).

-Stewart
Apr 10 '08 #2

P: 2
Stewart,

I'll try it out and let you know if that works! Thanks for your time and help!

Irving Guy
Apr 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.