Primary key - Appending and auto number to a set prefix

The UK's most regular instructor-led training courses.
Training information: microsoft+access+course · Microsoft access courses · Microsoft Access Training London
See also · microsoft access training · access vba courses · access database course

Resolved · Low Priority · Version 2003

Nam has attended:
Access Introduction course
Access Intermediate course
Access VBA course
Access Advanced course

by - delegate Nam [2 posts] (2008 Apr 7 Mon, 16:12) Reply

I have got 2 tables, tblClient and tblInvoicing on a one to many relationship respectively. How do I number my Invoice numbers (Primary ID field) such that they contain a prefix "PREM" then auto number of 6 fields e.g. PREM000001 for the first invoice number, PREM000010 for the tenth. How do I get the 4 letter prefix depending on the client e.g "PREM" for Premier, "CAST" for castlefield.

Version	Date	Location	Places available	Book	Next place rate:
Access Advanced 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Nov 26 Wed	Bloomsbury	3	book now	£224	£235
2007	2008 Nov 28 Fri	Bloomsbury	3	book now	£240	£245
2003	2008 Dec 3 Wed	Southwark	7	book now	£203	£230
2003	2008 Dec 19 Fri	Bloomsbury	6	book now	£214	£230
2003	2008 Dec 24 Wed	Bayswater	0	FULL
2003	2009 Jan 7 Wed	Southwark	7	book now	£203	£230
Full Schedule: See all 47 Access Advanced course dates. Bookings currently available until 25th November 2009.

RE: Primary key - Appending and auto number to a set prefix

by - trainer Pete platinum contributer [799 posts] (2008 Apr 7 Mon, 19:18) Edited on 2008 Apr 7 Mon, 22:06 Reply

Hi Nam, Thank you for the post, welcome to the forum, in answer to your question, try this, create your database or amend your existing database, in the ID field which I assume you have as an Autonumber field, (remember you can only link an autonumber to a Double Integer field), in the Table design view, select the ID field and in the Format line of the field properties enter the following for your Premier Table.

"PREM"000000

With the zeros being the number of numbers you require, for your other table obviously substitute the PREM with CAST,

I hope that works, it usually does, if so, please click the Resolved link, best regards Pete

· Quick Microsoft Access Course: Creating an Inventory Control Database in 8 Simple Steps
· How to Find a Third-Party Venue for Microsoft Access Training
· Take an Access Course to Learn About Relational Databases
· Access Database Optimization: First Normal Form
· Learn about Subqueries in Access Courses

Please browse our web site to find out more about
microsoft+access+course and other Microsoft training courses.

Access tip:

Modal forms

If you have created a form / switchboard and dont want people to get away from using the switchboard, you can change the form properites for Modal to On.

View all Access hints and tips