472,331 Members | 1,690 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,331 software developers and data experts.

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 2439
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

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

Similar topics

by: Ram | last post by:
Hi, I am trying to set up a DB for a race series where a riders best 6 of 10 rides count towards a league position. I have a table of Riders (...
by: John Baker | last post by:
Hi: At the outset let me admit that I screwed up! I have built a rather elaborate set of forms and sub forms starting with a client table, and...
by: Dragon | last post by:
When I run a make-table query, the results take over 1 minute to return. The query is making a table that has 12,000 records in it. If I run the...
by: Greg Strong | last post by:
Hello All, Is it possible to change table field lookup properties in code? I've been able to change other field properties in code, however so...
by: vonclausowitz | last post by:
Hi All, I was thinking of creating a table in my database to index all words in the database. That way I can quickly search for one or more...
by: daniellee2006 | last post by:
I am creating a basic website to store people profiles and within this website i have a page that creates a table dependent on the number of...
by: joshua.nicholes | last post by:
I have an access database that consists of two tables.A data collection table and a species list table. The data collection table has about 1500...
by: Wayne L | last post by:
I have searched and could not find my solution to my problem. I have a table that has a field that I want to start at 0001 and go to 1999. Once it...
by: ZaphodBBB | last post by:
Hi I have a table that has as its Primary Key the Auto-Increment Field: Equipment_Number. In M.S. Access is there any way to define a starting...
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.