Auto Increment for non integer ?

  • Thread starter Thread starter saleemMSMS
  • Start date Start date
S

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
 
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
 
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
 
Back
Top