Re: GENERATING AUTOMATIC NUMBERS

From: Graham Mandeno (Graham.Mandeno_at_nomail.please)
Date: 08/09/04 

Date: Mon, 9 Aug 2004 13:00:33 +1200

Hi James

I suggest you store two fields:
    RecordDate (long)
    RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long" instead of
"date/time" to ensure that it can only be a date part with no time) and
store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to ensure no
duplicates.

If you typically enter records on the date to which they apply, then set the
DefaultValue for RecordDate to "=Date()". Storing the date may have other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
    "RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ), 0) + 1

In plain English, find the maximum sequence number for the given date (and
if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be generated on
the fly in a calculated field in a query, form or report:
    IDString = Format( [RecordDate], "yy" ) _
        & Format( DatePart ( "y", [RecordDate] ), "000" ) _
        & Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the limit of 9999
records a day, all you need do is add another "0" to the format string and
the job is done. 

-- 
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
"JAMES FENNEL" <JAMESFENNEL@discussions.microsoft.com> wrote in message
news:3661597C-A16B-4265-B007-AAE02C983FA6@microsoft.com...
> I am responsible for keeping track of Safety discrepacies on board my
ship.
> I have a database to do so and I'm trying to automate some of the features
to
> make it easier.  I would like to automate the ID feature using a
Year-Julian
> Date-auto increment format.  Year in NN format, Julian Date in NNN format,
> Auto Increment in NNNN format (not to exceed 9999 and reset each new
julian
> date).  I would like this feature to key off when the the preceding field,
> INSPTR loses focus.  Can anybody out there help me?
>

Relevant Pages

  • Re: A PC for music management and storage.
    ... access my audio system to manage and store music etc. ... MP3 format, about 1 Mb per minute, or WAV ... Hard drives are amazingly ... Bus and RAM speed may be important. ...
    (rec.music.classical.recordings)
  • Re: Suggestion needed on data storage format in text file
    ... the mailbox file and store the summary in the text file for fast ... Then you run into the problem of someone who gets very verbose with their subject line and exceeds the 100 characters. ... Two other ways - use CSV format, which is well documented and supported by PHP and other programs. ...
    (comp.lang.php)
  • Re: Multiple date formats in a Table
    ... a date field could not store just the year 2004 - it would have ... display it as 1 Jan 2004, or you would have to just display the year for all ... The conversion of your existing text format date should be done in a query, ... Public Function TextToDate(strDate As String) As Date ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Max Function
    ... David: apparently my excel has a custom number format that I had forgotten ... Store number in A2:A10 ... criteria has been assigned a value and the Total row sums the total ...
    (microsoft.public.excel.worksheet.functions)
  • GENERATING AUTOMATIC NUMBERS
    ... I would like to automate the ID feature using a Year-Julian ... Date-auto increment format. ... Year in NN format, Julian Date in NNN format, ...
    (microsoft.public.access.formscoding)