Creating a customized counter?

[Peter]

I am creating a file tracking database and would like to
use our file numbering system, but have it autonumber.
For example, our filing system uses the current year and
then a four digit number starting at one such as
2004-0001 So I would like every record to autonumber to
create the new and unique file number 2004-0002, 2004-0003
etc.

Please help!

Peter.

 

[Petrucci2000]

Hi Peter,

You set the Format property of the AutoNumber equal to
\200"4-"0000

Other ideas include:
209830 ACC2000: How to Increment the Numeric Portion of a String
http://support.microsoft.com/?id=209830

199679 ACC2000: How to Simulate a Dynamic Counter in a Table or a Query to
http://support.microsoft.com/?id=199679

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| Content-Class: urn:content-classes:message
| From: "Peter" <[email protected]>
| Sender: "Peter" <[email protected]>
| Subject: Creating a customized counter?
| Date: Mon, 19 Jan 2004 14:39:19 -0800
| Lines: 11
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcPe3ROoRnbpFwgVQ1W7xlVZbTNWqQ==
| Newsgroups: microsoft.public.access.tablesdbdesign
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.tablesdbdesign:74428
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| I am creating a file tracking database and would like to
| use our file numbering system, but have it autonumber.
| For example, our filing system uses the current year and
| then a four digit number starting at one such as
| 2004-0001 So I would like every record to autonumber to
| create the new and unique file number 2004-0002, 2004-0003
| etc.
|
| Please help!
|
| Peter.
|

 

[Peter]

Thanks Eric! That worked perfectly. Do you have any
suggestions on how to reset the Autonumber?

 

[Peter]

Hi Eric, you can ignore my last question about re-setting
the counter. I deleted the first ID counter and created a
second which effectively accomplished what I was looking
to do. Thanks again!

 

[John Vinson]

I am creating a file tracking database and would like to
use our file numbering system, but have it autonumber.
For example, our filing system uses the current year and
then a four digit number starting at one such as
2004-0001 So I would like every record to autonumber to
create the new and unique file number 2004-0002, 2004-0003
etc.

An Autonumber will NOT work for this. And... this kind of "Intelligent
Key" is generally frowned upon; storing two pieces of data in one
field is unwise, and storing data (a date) in an ID field violates
first normal form - fields should be atomic.

If you want this number anyway, for compatibility with an existing
paper system for instance, I'd suggest using *two* fields, FileYear
and FileSeq, both Integer. Set the Default property of FileYear to

Year(Date())

so it will automatically pick up the current year; and use a Form to
enter data into your table (table datasheets don't have any usable
events). Put a textbox on the form named txtFileSeq bound to the
FileSeq field. In the Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtFileSeq = NZ(DMax("[FileSeq]", "[your-table-name]", "[FileYear]
= " & Me!FileYear))
End Sub

 

[John Vinson]

Hi Peter,

You set the Format property of the AutoNumber equal to
\200"4-"0000

Note that this will continue to display the number as 2004-xxxx in
2005, 2006 and later years, and that the number will continue to
increment rather than starting over at the beginning of the year!
Peter should use one of your other suggestions, in order to meet the
real requirement.

 

[prabha]

Hi Peter,

John's correct in that the number will display as 2004-xxxx in 2005, 2006
and later years, and that the number will continue to increment rather than
starting over at the beginning of the year!

Look at his suggestion in his previous post if you are looking for it to
start over at the beginning of the year.

Eric


--------------------
| From: John Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
| Newsgroups: microsoft.public.access.tablesdbdesign
| Subject: Re: Creating a customized counter?
| Date: Mon, 19 Jan 2004 18:26:25 -0700
| Reply-To: (e-mail address removed)
| Message-ID: <[email protected]>
| References: <[email protected]>
<[email protected]>
| X-Newsreader: Forte Agent 1.8/32.548
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| NNTP-Posting-Host: 10.6.1.33
| X-Original-NNTP-Posting-Host: 10.6.1.33
| X-Trace: 19 Jan 2004 18:22:26 -0700, 10.6.1.33
| X-Original-Trace: 19 Jan 2004 18:22:26 -0700, 10.6.1.33
| Lines: 24
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix
..com!newsfeed.cwix.com!news.maxwell.syr.edu!news.glorb.com!newshosting.com!n
x02.iad01.newshosting.com!news-feed01.roc.ny.frontiernet.net!nntp.frontierne
t.net!news-west.eli.net!nntp.widaho.net!10.6.1.33
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.tablesdbdesign:74440
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| On Mon, 19 Jan 2004 23:59:47 GMT, (e-mail address removed)
| ("Petrucci2000") wrote:
|
| >Hi Peter,
| >
| >You set the Format property of the AutoNumber equal to
| > \200"4-"0000
|
| Note that this will continue to display the number as 2004-xxxx in
| 2005, 2006 and later years, and that the number will continue to
| increment rather than starting over at the beginning of the year!
| Peter should use one of your other suggestions, in order to meet the
| real requirement.
|
| >Other ideas include:
| >209830 ACC2000: How to Increment the Numeric Portion of a String
| >http://support.microsoft.com/?id=209830
| >
| >199679 ACC2000: How to Simulate a Dynamic Counter in a Table or a Query
to
| >http://support.microsoft.com/?id=199679
|
| John W. Vinson[MVP]
| Come for live chats every Tuesday and Thursday
| http://go.compuserve.com/msdevapps?loc=us&access=public
|

 

[Peter]

Hi John,
Thanks so much for the recommendations on my problem. I
used your code at the bottom and got it to work. I was
wondering if the code you provided will also reset the
autonumber sequence (i.e. your "FileSeq") to zero once the
year automatically changes. In other words, when 2005
comes...will the sequence reset to look like 2005-0001?
Or will it continue counting where the autonumber left
off, only with the 2005 prefix (ie 2005-1969)? I'm very
new at Access and programming, but it looks like it might
be the latter...if so, do you have any suggestions on how
I might change it to reset with the new year?

Sorry, to bug you like this. I've just been tasked with
something over my head and could really use your help.
Cheers!

-----Original Message-----

I am creating a file tracking database and would like to
use our file numbering system, but have it autonumber.
For example, our filing system uses the current year and
then a four digit number starting at one such as
2004-0001 So I would like every record to autonumber to
create the new and unique file number 2004-0002, 2004- 0003
etc.

An Autonumber will NOT work for this. And... this kind of "Intelligent
Key" is generally frowned upon; storing two pieces of data in one
field is unwise, and storing data (a date) in an ID field violates
first normal form - fields should be atomic.

If you want this number anyway, for compatibility with an existing
paper system for instance, I'd suggest using *two* fields, FileYear
and FileSeq, both Integer. Set the Default property of FileYear to

Year(Date())

so it will automatically pick up the current year; and use a Form to
enter data into your table (table datasheets don't have any usable
events). Put a textbox on the form named txtFileSeq bound to the
FileSeq field. In the Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtFileSeq = NZ(DMax("[FileSeq]", "[your-table- name]", "[FileYear]
= " & Me!FileYear))
End Sub



.

 

[John Vinson]

Thanks so much for the recommendations on my problem. I
used your code at the bottom and got it to work. I was
wondering if the code you provided will also reset the
autonumber sequence (i.e. your "FileSeq") to zero once the
year automatically changes. In other words, when 2005
comes...will the sequence reset to look like 2005-0001?

Yes, it will: let me explain why.

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtFileSeq = NZ(DMax("[FileSeq]", "[your-table-name]", _
"[FileYear]= " & Me!FileYear)) + 1
End Sub

The DMax() function takes three arguments: a field to be looked up; a
"domain" - a table or query in which to look; and a criterion as the
third argument.

The criterion will search for all records in your-table-name for which
the table's FileYear field is equal to the FileYear control on the
form. If it's 2005, that control will (presumably) contain 2005, so it
will find all the 2005 records, if any. If there are already some
records in 2005, the DMax() function will return the largest value of
FileSeq in that group; if there aren't any at all, it will return
NULL.

The NZ() function will either return the maximum existing FileSeq
value, or 0 if DMax() didn't return anything - that's what NZ does,
converts Null to Zero.

Finally the expression (as I've corrected it here... as written it
would NOT work!!!) will add 1 to whatever was returned by NZ; if there
were no 2005 records you'll get 0 + 1 and txtFileSeq will be set to 1,
otherwise it will add one to the largest existing value and use that.

 

[Peter]

Thanks again for taking the time to provide both a
solution and explaination. However, it seems that all the
feedback i've been receiving indicates I shouldn't be
using an autonumber for this purpose in the first place.
But I appreciate all the help!

Cheers,
Peter.

-----Original Message-----

Thanks so much for the recommendations on my problem. I
used your code at the bottom and got it to work. I was
wondering if the code you provided will also reset the
autonumber sequence (i.e. your "FileSeq") to zero once the
year automatically changes. In other words, when 2005
comes...will the sequence reset to look like 2005-0001?

Yes, it will: let me explain why.

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtFileSeq = NZ(DMax("[FileSeq]", "[your-table-name]", _
"[FileYear]= " & Me!FileYear)) + 1
End Sub

The DMax() function takes three arguments: a field to be looked up; a
"domain" - a table or query in which to look; and a criterion as the
third argument.

The criterion will search for all records in your-table- name for which
the table's FileYear field is equal to the FileYear control on the
form. If it's 2005, that control will (presumably) contain 2005, so it
will find all the 2005 records, if any. If there are already some
records in 2005, the DMax() function will return the largest value of
FileSeq in that group; if there aren't any at all, it will return
NULL.

The NZ() function will either return the maximum existing FileSeq
value, or 0 if DMax() didn't return anything - that's what NZ does,
converts Null to Zero.

Finally the expression (as I've corrected it here... as written it
would NOT work!!!) will add 1 to whatever was returned by NZ; if there
were no 2005 records you'll get 0 + 1 and txtFileSeq will be set to 1,
otherwise it will add one to the largest existing value and use that.




.

 

[John Vinson]

Thanks again for taking the time to provide both a
solution and explaination. However, it seems that all the
feedback i've been receiving indicates I shouldn't be
using an autonumber for this purpose in the first place.

ummmm... exactly.

My suggestion does NOT use an Autonumber. It's an alternative to using
an Autonumber. Please reread my posts!