Custom Auto Number

  • Thread starter Thread starter rundmcnj
  • Start date Start date
R

rundmcnj

I want to creat a custom auto number starting at
2008-0001, 2008-0002,2008-0003 etc.

Suggestions
 
Try this: start a new query in design view. Make it a make-table query. go to
sql view and type.
Alter table yourtablename
add column ID Counter (2008-1000,2008-0001)
Run the query
 
It appears you want to use the year in the number. In this case, I recommend
two Long Integer fields. One to store the year and one to store the
sequential number.
Assume:
Me.txtTheYear contains the year you want to use
Me.txtCustomAuto is where you will present the next number for the year
[YearField] is the name of the field in the table to store the year
[SeqNbr] is the name ifle field in the table to store the sequential number


lngSeqNumber = Nz(DMax("[SegNbr]", "YourTable", "[YearField] = " &
Me.txtTheYear),0) + 1
MetxtCustomAuto = Me.txtTheYear & Format(lngSeqNumber,\-0000")

No need to create or maintain a table.
 
Concur with Ray that you need to split this into two fields.

Is this a single or multi-user database? If single user, then Dave's
(Klatuu) sugestion works great.

If, however, you have multiple users, and they are likely to be working on
the same form, different records at the same time, the potential exists that
you could create two records with the same value. If this is your situation,
post back and we can provide an alternative methodology, or more guidance.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top