Prev: Combo box in form error
Next: Compacting Question
From: Stimpy707 on 2 Jul 2008 14:32
Thanks in advance for any help someone may be able to offer.

I would like to create a three number field that increments by one digit but
is preceeded by the last two digits of the current year and a hyphen, such as
08-001, 08-002, 08-003. We would fill out individual reports throughout 2008
and then in 2009 the format would automatically change to 09-###, 09-###+1,
etc.

The primary key is set to NCRID and the field I want to create the 08-###
format is called ReportNumber.

I would be happy to hear if anyone has different suggestions on how to create

Here are a few fields of my table...

tblNonConformanceReport
NCRID
ReportNumber
Customer
etc...

Thank You
From: Jeff Boyce on 2 Jul 2008 15:01
So, although you are using the term "report", you aren't necessarily
referring to what Access considers a "report", right?

You can add a field that holds a sequence number. Then you'd use a query to
concatenate the last two characters of the record's date/time 'year' with a
hyphen and the sequence number, formatted as "00#". No need to store all
that, but you will need the sequence number and the date/time value.

Check mvps.org/access for "Custom Autonumber" to get some ideas of how to
build something like this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Stimpy707" <Stimpy707(a)discussions.microsoft.com> wrote in message
news:1B510D52-FCE2-4FBF-BE18-B9DEDF29F595(a)microsoft.com...
> Thanks in advance for any help someone may be able to offer.
>
> I would like to create a three number field that increments by one digit
> but
> is preceeded by the last two digits of the current year and a hyphen, such
> as
> 08-001, 08-002, 08-003. We would fill out individual reports throughout
> 2008
> and then in 2009 the format would automatically change to 09-###,
> 09-###+1,
> etc.
>
> The primary key is set to NCRID and the field I want to create the 08-###
> format is called ReportNumber.
>
> I would be happy to hear if anyone has different suggestions on how to
> create
>
> Here are a few fields of my table...
>
> tblNonConformanceReport
> NCRID
> ReportNumber
> Customer
> etc...
>
> Thank You


From: Klatuu on 2 Jul 2008 15:18
You need an additional field for report year.
To get the next available number you can use:

= Nz(DMax("NCRID","tblNonConformanceReport","ReportYear = " & Year(Date)),
0) + 1

Now, there can be one problem with this method depending on how many users
may be creating reports at the same time. It is possible for one user to get
the number and be entering data for the report, but before she saves it,
another user starting a report will get the same number. You have to allow
for that possibility. How you resolve it depends on the likelyhood of it
happening and whether you use the report number as part of a unique index.


--
Dave Hargis, Microsoft Access MVP


"Stimpy707" wrote:

> Thanks in advance for any help someone may be able to offer.
>
> I would like to create a three number field that increments by one digit but
> is preceeded by the last two digits of the current year and a hyphen, such as
> 08-001, 08-002, 08-003. We would fill out individual reports throughout 2008
> and then in 2009 the format would automatically change to 09-###, 09-###+1,
> etc.
>
> The primary key is set to NCRID and the field I want to create the 08-###
> format is called ReportNumber.
>
> I would be happy to hear if anyone has different suggestions on how to create
>
> Here are a few fields of my table...
>
> tblNonConformanceReport
> NCRID
> ReportNumber
> Customer
> etc...
>
> Thank You
 | 
Pages: 1
Prev: Combo box in form error
Next: Compacting Question