Auto Increment for non integer ?

[saleemMSMS]

hi i have a table called invoice. the primary key supposed to be not only a
number, but also a string. i thought of making the primary key as follows.
INV001, INV002 etc. is there a way to auto increment such string values ? if
yes how to ? if no, can anyone suggest me a way of having a sequential yet
string attached primary key ?
thanx

 

[Tom van Stiphout]

On Tue, 11 Aug 2009 21:04:01 -0700, saleemMSMS

This is a FAQ. You should have no problem finding related posts in
groups.google.com. You will find:
* It often does not make sense to assign meaning to a PK - it
typically is used for internal bookkeeping and not exposed to users
* You can format a number in your pattern:
select "INV" & Format(myPkValue, "000") from myTable
(of course you replace myObjectNames with yours, and keep your fingers
crossed you never have more than 999 invoice numbers.

-Tom.
Microsoft Access MVP

 

[Klatuu]

If the string part will always be INV, why include it in the table? It can
always be added to the number for display on forms and reports. And, do not
use an autonumber field for the numbers. Autonumber fields should only be
used as surrogate keys for relating tables.

It increment a numeric field, use the form's Current event to find the
current highest number and add 1 to it.

If Me.NewRec then
Me.txtInvNo = Nz(DMax("[INV_NO]","[tblInvoice]"), 0) + 1
End If