Custom Counter

[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.

[Rick Brandt]

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

[Rick Brandt]

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

[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

[Rick Brandt]

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