By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Custom Counter

P: n/a
WEG
I want to create a custom counter to act as the primary field in a table but
am having trouble with it.

What I would like is a counter that shows the last two digits of the year, a
hyphen, then a counter that starts at one and goes up by one digit with each
new record. Then, when a new year starts, would reset to one and start
again.

For instance, 07-001, 07-002 and so on until 08-001, 08-002. I've seen all
kinds of great answers here, so I thank you in advance for the great answer
I'm sure you will give me.
Jun 12 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
WEG wrote:
I want to create a custom counter to act as the primary field in a
table but am having trouble with it.

What I would like is a counter that shows the last two digits of the
year, a hyphen, then a counter that starts at one and goes up by one
digit with each new record. Then, when a new year starts, would
reset to one and start again.

For instance, 07-001, 07-002 and so on until 08-001, 08-002. I've
seen all kinds of great answers here, so I thank you in advance for
the great answer I'm sure you will give me.
Better is to have two fields. One that holds the DateTime of record creation
and one that holds an incrementing number. You can then display what you want
with an expression like...

=Format(CreatedOn,"yy\-") & Format(ID, "000")

The CreatedOn field simply needs a default value of Now(). For the ID use
DMax() in the BeforeUpdate event of the form.

If Me.NewRecord Then
Me.ID = Nz(DMax,"ID", "TableName", Year(CreatedOn) = Year(Date())"),0) + 1
End If
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 12 '07 #2

P: n/a
Rick Brandt wrote:

Missed a double quote below.
If Me.NewRecord Then
Me.ID = Nz(DMax,"ID", "TableName", Year(CreatedOn) = Year(Date())"),0)
+ 1 End If
Should be...

If Me.NewRecord Then
Me.ID = Nz(DMax,"ID", "TableName", "Year(CreatedOn) = Year(Date())"),0) +
1
End If


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 12 '07 #3

P: n/a
WEG
Thanks, that did the trick.

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:dZ**************@newssvr17.news.prodigy.net.. .
Rick Brandt wrote:

Missed a double quote below.
>If Me.NewRecord Then
Me.ID = Nz(DMax,"ID", "TableName", Year(CreatedOn) = Year(Date())"),0)
+ 1 End If

Should be...

If Me.NewRecord Then
Me.ID = Nz(DMax,"ID", "TableName", "Year(CreatedOn) = Year(Date())"),0)
+ 1
End If


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Jun 13 '07 #4

P: n/a
WEG wrote:
Thanks, that did the trick.
Should be...

If Me.NewRecord Then
Me.ID = Nz(DMax,"ID", "TableName", "Year(CreatedOn) =
Year(Date())"),0) + 1
End If
Okay, now that you have it working I will give you an improvement. I use the
syntax above because it is easier to understand and shorter so line-wrapping
doesn't make too big a mess of it. However; what would be more efficient would
be to make sure you have an index on the CreatedOn field and use this for the
WHERE clause instead of what I gave you before.

"CreatedOn >= DateSerial(Year(Date()), 1, 1) AND CreatedOn <
DateSerial(Year(Date()) + 1, 1, 1)"

The reason is that whenever you apply criteria to an expression it is
inefficient and cannot use an index. The revised syntax above applies the
criteria directly to the field value so an index can be used and efficiency is
much greater. This would be especially important as your table gets larger.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 13 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.