Re: Self-generating Number

trafalgar1978@xxxxxxxxx wrote:
Good evening everyone,

I built a database to track reprocessing of parts that were fixed
where I work. We are required by instruction to serialize these parts
as they come back in the following format. Last 2 digits of the year
and then the # it came back as. An example of the Serial # is
08-0122. I want to make the database automatically add to this
number
and generate a new one each time a new record is created. i have
tried
several different ways to accomplish this but nothing has worked thus
far. Any help on this issue would be greatly appreciated.


Traf

Best would be to *store* the value in two fields. A RecordDate field and a
RecordID field. You can then use an expression to *display* a value that
uses both fields as YY-0000.

This makes is much easier and much more efficient to calculate the next
numeric value when a record is created. To do so with a string field that
contains the single value "YY-0000" will be more difficult and slower.

With the two field approach you just use this for display...

=Format(RecordDate, "YY-") & Format(RecordID,"0000")

You set RecordDate's DefaultValue property to =Now() and to calculate the
next available value in a new record you use the BeforeUpdate event of the
form...

If Me.NewRecord Then
Me.RecordID = Nz(DMax("RecordID", "TableName", "Year(RecordDate) =
Year(Date())"), 0) + 1
End If

Broken down the expression finds the highest existing RecordID for records
created this year and adds 1 to it. Zero is substituted for the lookup to
account for the first record of each new year.

The WHERE argument in the above example was used because it is shorter and
easier to understand. A more verbose, but more efficient WHERE argument
would be...

"RecordDate >= DateSerial(Year(Date()), 1, 1) AND RecordDate <
DateSerial(Year(Date()) + 1, 1,1)"

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


.

Relevant Pages

  • Self-generating Number
    ... I built a database to track reprocessing of parts that were fixed ... We are required by instruction to serialize these parts ...
    (microsoft.public.access.queries)
  • Re: [PHP] Writing my own web based user forum
    ... Since it seems you are using a database there is no need to serialize. ... In your original post you did not mention a "picture upload site" so I ... I am looking at writing my own web based user forum somewhat for my own ...
    (php.general)
  • Re: Whats a good database for a really simple app?
    ... you could also create the stuff as a object database and just serialize ... > if it's on disk at startup, create it if not, and serialize it as the ... > anyone needing even a two user app, but for a single user app it ...
    (microsoft.public.dotnet.languages.vb)
  • Serialization/Compression
    ... envisaged that we are going to run out of database space if more users start ... approach is when the user modifies the data. ... If the user has edited the data i will have to find out which chunk he ... serialize and compress only that portion of the data. ...
    (microsoft.public.dotnet.general)
  • Re: Workers and Cycles
    ... What strikes me is that you use WFL in the question. ... into a similar instruction set. ... Database Server for ClearPath MCP"? ... Alan Ponting- Hide quoted text - ...
    (comp.sys.unisys)