Re: Adding a contract number



Bick71 wrote:

I have very limited experience with Access, and by very limited I mean
almost none. I know Access is the program I need for keeping track of
the type of information I am trying to manage and I've been trying to
learn it but it's just frustrating me and I have no one around me with
any access knowledge to assist me.

I'm trying to replace 6 sets of Excel spreadsheets with one Access
database, and I have a grip on most of what I want to do because most
are straight text fields that will be data entry.

Getting a contract number to auto-populate and auto-generate is
eluding me. I want the contract number to be a 6 digit number (it
must ONLY be 6 digits because this number is to be used by others who
are limited to 6 characters) that includes the fiscal year (last 2 or
first 2 digits is fine) and a 4 digit number. So for example this
coming July 1, 2007 my number, in a perfect world, would be 08-0001.
If I assigned 1576 contracts from July 1st thru June 30th my last
number would be 08-1576 and on July 1, 2008 the number would then
automatically change to 09-0001 and go up from there.

Being limited to only 9999 numbers in any given fiscal year is not an
issue, I doubt we would ever go over 2500. Having the year a 2 digit
number is also not an issue, we will not be entering in anything in
the past and the year 2101 is a good ways off.

If anyone can help me do this & explain it so a 4 year old could
understand I would be eternally grateful.


Use two fields for these two parts of the contract number,
one for the contract date (might be useful for other things
too) and one for the sequential number.

Since your fiscal year appears to begin on July 1, you can
calculate the FY part by using:
Year(DateAdd("m", 6, ContractDate))

The sequential number field can be filled in by using a
little code in the form's BeforeUpdate event procedure:

Me. SeqNo = Nz(DMax("SeqNo", "Contracts", _
"Year(DateAdd("m", 6, ContractDate)) >= " _
& Year(DateAdd("m", 6, Date()))), 0) + 1

Then, whenever you need to display or export the contract
number, use this kind of expression in a calculated field in
a query or in a form/report text box:

=Format(Year(DateAdd("m", 6, ContractDate)), "00") &
Format(SeqNo, "0000")

--
Marsh
MVP [MS Access]
.



Relevant Pages

  • Re: Adding a contract number
    ... I want the contract number to be a 6 digit number (it ... Use two fields for these two parts of the contract number, ... any VBA functions that you are not familiar with. ...
    (microsoft.public.access.formscoding)
  • Re: Adding a contract number
    ... eluding me. ... I want the contract number to be a 6 digit number (it ... are limited to 6 characters) that includes the fiscal year (last 2 or ... Use two fields for these two parts of the contract number, ...
    (microsoft.public.access.formscoding)
  • Adding a contract number
    ... I have very limited experience with Access, and by very limited I mean ... Getting a contract number to auto-populate and auto-generate is ... I want the contract number to be a 6 digit number (it ...
    (microsoft.public.access.formscoding)