Increment custom number format ie. 080100, 080101 (yy####)

Using two columns (fields) makes it a lot easier. Both fields should be
integer number data type and named ReportYear and ReportNumber say. Set the
DefaultValue property of the ReportYear column to:

Year(Date())

The user will never see the values in either of these columns as in forms,
reports or queries you'd show the full report number. In an unbound computed
column in a form or report for instance its ControlSource property would be:

= Right(CStr(ReportYear),2) & Format(ReportNumber,"0000")

In the form's BeforeInsert event procedure put:

Dim strCriteria as String

strCriteria = "ReportYear = " & Year(Date())

Me.ReportNumber = Nz(DMax("ReportNumber", "YourTableName", strCriteria),0)

This will start the number sequence at 1 for each year, which I think is
what you had in mind.

Some people might argue that the report 'number' is not really a number in
the true sense but an encoding system, and it would therefore be more
appropriate to use a text data type. I would disagree as the number has both
an ordinal significance (the order in which the reports are produced) and a
cardinal significance ( the latest number per year is the number produced
that year) so should be an integer number data type. The computed
combination of the two values *is* an encoding system of course.

One caveat: in a multi-user environment two users adding records
simultaneously could get the same number. You should guard against this by
indexing the ReportYear and ReportNumber columns uniquely; that's an index on
both columns together, not individually. The simplest way is to make them
the composite primary key of the table, which you can do in table design view
by dragging down over both fields to highlight them, right-click and select
primary key from the shortcut menu. If a conflict does then occur the second
user attempting to save the record would trigger a data error and the illegal
record would not be saved.

Even if this application is not in a multi-user environment the columns
should be indexed uniquely of course.

Apologies in advance if you respond to this and I don't get back to you, but
after tomorrow I'll be away incommunicado for a while.

Ken Sheridan
Stafford, England

"Stimpy707" wrote:

> I've seen similar posts but I still need a little help.
> If you have a better suggestion based on something you have used please let
> me know. I'm always open to different ideas. Also, I'm an Access newbie so
> you need to type slow and loud
>
> Every so often, we will be using a form to record manufacturing results. I
> would like to create a field that gives the report a number when it is
> created.
>
> The previous database (that has since died of unnatural causes) was set up
> with the two-digit year followed by four digits. 080100 would represent the
> year 2008, report number 0100. The next report would be 080101, and so on.
>
> After yy9999, I imagine that the four digit number could begin again at 0000
> but it's not important because we fill out so few of these it would take
> hundreds of years for that to happen.
>
> Thanks in advance.