Re: Sequential number

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1


"Scott" wrote:

Steve,

Thanks for your suggestion. For the second part, each year will not go
beyond 9999 - far below 9999 at the moment. It requires to reset to 0001
each year automatically. Both the second part and the third part may be
duplicate and need to number sequentially. I am unsure how to control them
automatically. Any idea?

Thanks,

Scott

"[MVP] S.Clark" <steve.clark_NOSPAM@xxxxxxxxxx> wrote in message
news:e3JRsHjYGHA.500@xxxxxxxxxxxxxxxxxxxxxxx
The first part, you have handled.

For the second part, I would create a table with an AutoNumber field.
When you need a number, you write a record to the table(which increments
the number), and use that as the basis for the 4-digit number. This will
work around any multiuser locking issues that you might have. (I don't
know what you'll do when you get to 9999, but maybe that's not an issue)
Also store things like date created, user id, etc.

The Third part would be a child table to the second part table. If
mulitple users can generate these part numbers for the same report, then
you'll need to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it altogether.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

"Scott" <NoSpam-Scott.Xe@xxxxxxxxx> wrote in message
news:%23uRxUOdYGHA.460@xxxxxxxxxxxxxxxxxxxxxxx
I need to have a field to generate the report number with the following
format:-

The first part consists of four digits for each year. It could be
obtained from the date field entered manually.

The second part consists of four digits for the report number of the
year. It should be generated automatically when a record is created. The
number must be incremented by 1 for each new record and not repeated in
the year but can be repeated each year.

The third part consists of a single digit for each part of report number,
ie, -1, -2, -3. It should be generated automatically when a record is
created. The number must be incremented by 1 for each new record and not
be repeated in a report number but can be repeated in other report
numbers.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott








.

Relevant Pages

  • Re: Sequential number
    ... The first part consists of four digits for each year. ... The second part consists of four digits for the report number of the ...
    (microsoft.public.access.queries)
  • Re: Sequential number
    ... caution I have to take in multiuser environment in where I am going to use. ... mulitple users can generate these part numbers for the same report, ... The first part consists of four digits for each year. ...
    (microsoft.public.access.queries)
  • Re: Sequential number
    ... This may cause dups in a multiuser environment. ... The first part consists of four digits for each year. ... The second part consists of four digits for the report number of the ...
    (microsoft.public.access.queries)
  • Re: Sequential number
    ... The first part consists of four digits for each year. ... The second part consists of four digits for the report number of the ...
    (microsoft.public.access.queries)
  • Re: Hey! Theres more than 2 digits...
    ... only have 2 'real' digits and 2 zeros. ... appropriate report ... >>have to contact the client for ALL discrepancies to ... How can I check for extra digits? ...
    (microsoft.public.access.reports)
Loading