469,638 Members | 1,567 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,638 developers. It's quick & easy.

A table with Auto number generation field

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
2 2326
Stewart Ross
2,545 Expert Mod 2GB
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).

Apr 10 '08 #2

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.

Similar topics

3 posts views Thread by Ram | last post: by
2 posts views Thread by Dragon | last post: by
3 posts views Thread by vonclausowitz | last post: by
1 post views Thread by daniellee2006 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.