Unique number

[BRUNOCLAESSENS]

I want to make a unique number for an orderid<br>as follows<br>ex. date is 28/07/2000 then <br>first order must be 2000072801<br>second&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2000072802&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br><br>first order of 29/07/2000&nbsp;&nbsp;&nbsp;2000072901<br><br>etc...<br><br>and where do I have to place it? <br>Defaultvalue, event (on focus, on enter,...)<br><br>thanks <br>Bruno<br>

 

[MichaelRed]

Bruno,<br><br>The more I thought about it the more trouble I see.&nbsp;&nbsp;It can be confusing to be looking at the &quot;numbers&quot; you create this way.&nbsp;&nbsp;for each &quot;decade&quot; of orders, the length of the &quot;OrderID&quot; will change (e.g 200007281 {first}, 2000072810 {tenth}, 20000728100 {One-Hundreth)...).<br><br>To address the issue, It is usually necessary to create a seperate table to hold any unique value that is assigned at run time.&nbsp;&nbsp;Each &quot;assignment&quot; needs to open this table in EXCLUSIVE access mode, get the current last value, add the next value and close/release the file.&nbsp;&nbsp;On heavily used systems, you need to take some care that the request to open the file has a &quot;retry&quot; mechanisim - users WILL attempt to get the next number of the file while it is in use, and they need to (programatically) have the patience to wait for the previous user to finish.<br><br>To concatenate the date stamp with the sequential number&quot;<br><br>OrderId = Format(Now, &quot;yyyymmdd&quot & Trim(Str(NumVar))<br>(assuming that &quot;OrderID&quot; is the name of the STRING variable/Field to hold the results and &quot;NumVar&quot; is the sequential value to be added (e.g OrderID for the 100th order for today (July 28th 2000) the above will return &quot;2000072810&quot;.<br><br>To get the above as a numeric, just&nbsp;&nbsp;wrap the results in VAL() [e.g. OrderID = Val(Format(Now, &quot;yyyymmdd&quot & Numvar].<br><br>Back to the original point.&nbsp;&nbsp;The necessity of having a seperate Table for the Unique value cannot be overemphasized!!&nbsp;&nbsp;I most cases, IU have even set up a seperate DATABASE - just to have the access to the unique value be explicitly and completly restricted to a single user.&nbsp;&nbsp;What value you place in the field does NOT really matter as much as the need for EXCLUSIVE access to this value.&nbsp;&nbsp;Also, in your case (i.e. using the date as part of the ID) you need to take EXTRME measures to assure that the Unique value table is reinitalized for each date.&nbsp;&nbsp;This - further - implaes that you need to maintain a date field in the unique value table, so it can be checked.&nbsp;&nbsp;This also implies the need to make sure that the users of this data base are all clock synchronized.&nbsp;&nbsp;This last point is usually not the responsibility of database programmers, but is handled by network system administrators.&nbsp;&nbsp;These folks may have other consideration in the setting of system clocks (some users in different time zones?) and NOT be really receptive to your need to synchronize all of the system's clocks.<br><br>Hmmmm - seems like I've written a book and probably not really answered your question.&nbsp;&nbsp;Hopefully somewhere in here you will find something useful, or at least thought provoking.<br><br>Michael