I have a form with an autonumber. I would like the umber to be formated to
indicate the following number. I want it to read as follows;
year-month-###(04-10-001). Can someone tell me how this is done.
Well... if you're wise, it won't be done that way. This is called an
"intelligent key" - and that's not a compliment. Dates are DATA;
storing two disparate pieces of data in one field - particularly a key
- is a Very Bad Idea. It will be very hard to maintain, or to use as a
link to other tables.
If - and ONLY if - this is needed for compatibility with a firmly
established paper system, I'd suggest using TWO fields, not one; the
first would be a date/time field ItemDate (with a default value of
Date() if you want each record to contain the date that it was
created), and the second being an Integer (or Long Integer) number
field I'll call SEQ. Ensure that ALL data entry is done using a Form,
using Access security to do so if need be. In the Form's BeforeInsert
event put code like
Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeq = NZ(DMax("[Seq]", "[your-table]", _
"[ItemDate] = #" & Date & "#")) + 1
End Sub
You can then use a textbox with a control source of
=Format([ItemDate], "yy-mm-") & Format([Seq], "000")
to display (but not to edit) the composite value.
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps