On Wed, 5 Dec 2007 14:19:02 -0800, Derek French <Derek
I want to setup a P.O. # field that takes the date the order was placed and
combines it with an incrementing number. I would like it to look like
120507-001, 120507-002, etc.
That's generally NOT a good idea, unless it's required for compatibility with
a well-established manual system. Fields should be "atomic", having one and
only one value.
If you really need this, I'd suggest using two fields: a date/time field with
a default value of Date() (don't use Now() as the default, since that will
store the date and the exact time); and a Number field to store the
incrementing value. You can use a Form to do your data entry, and in its
BeforeInsert event put code like
Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeq = NZ(DMax("[Seq]", "PurchaseOrders", "[PurchaseDate] = #" & _
Format(Date(), "mm/dd/yyyy") & "#")) + 1
If Me!txtSeq > 1000 Then
MsgBox "Go home. No more PO's today, out of room for number.", vbOKOnly
Cancel = True
End If
End Sub
You can combine the date and seq fields for display purposes if you wish:
=Format([PurchaseDate], "mmddyy") & "-" & Format([Seq], "000")
John W. Vinson [MVP]
