Adding a contract number

[Bick71]

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.

 

[Marshall Barton]

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")

 

[Bick71]

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]- Hide quoted text -

- Show quoted text -

I appreciate the help, but I am at the baby-step level. Is there
anyway you could break this down to something a true novice can
understand? Access is new to me and even the explanation you have
provided is over my head.

I very much appreciate the effort and I'm trying on my end, but it's
like a whole new language to me.

 

[Marshall Barton]

I appreciate the help, but I am at the baby-step level. Is there
anyway you could break this down to something a true novice can
understand? Access is new to me and even the explanation you have
provided is over my head.

I very much appreciate the effort and I'm trying on my end, but it's
like a whole new language to me.

Step 1) Open Access, hit Ctrl+g to open the VBA window,
Click Help - MS Visual Basic Help. Search for the topic for
any VBA functions that you are not familiar with. Try to
figure out how each function contributes to a solution of
the problem you posted.

Step 2) After you have done your homework ;-) post back
here with detailed questions about a specific aspect that
you need additional help to understand.

These news groups are not a useful medium for tutorials. To
get useful answers, you need to ask a detailed question that
can be answered in one or two paragraphs. In other words,
there is no way that I can respond to a question like "break
this down to" some undefined level of knowledge. That's
what class instructors and books are supposed to do.