Generate Sequence String
Seeking a sequence incrementing/generating model for an Access 2000 Application.
The sequence is an Order Number (string) and provides an informational meaning for my
client. Here is an example 03-2006-001S. The 03 represents the month. 2006 is the year,
001 represents the first 'S-type' order in the month of March 2006 and S represents the order
type. There are 3 types of orders: Services (S), Goods (G) and Consulting(C).
Therefore, one of the difficulties is that the sequence begins at 001 at the start of
the month and there are separate numbers for the order types. Some examples:
The order number (OrderNumber) is kept in an orders table (tblOrders).
The autoincrementing PK index in that table is called OrderID. For our purposes
assume there are three other fields: OrderDate (defaults to current Date), OrderCode (S,G,C
referenced above - actually another related table has these values in a numeric form S=1,
G=2, C=3) and customerName.
I am using a custom form wizard to lead the end-user through the data entry process. The
first page of the wizard collects/sets the OrderID, the OrderCode and the CustomerName. This
basically saves the record. The order number is created on the on-click event of the "Next"
button of the wizard so that the Order Number is displayed on the second page of the
Unfortunately, I don't understand how to generate [url removed, login to view] datepart function and Format function
e.g. Format$(001,"000") can be used for part of the string, but it appears that I
will need additonal fields and logic to solve this puzzle.
Your ability to work on this immediately will impact selection process.
1) working prototype example of generator in Access 2000.
2) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).