change autonumber with date

[hpmarc]

Hello everyone!

I am in need of some help. I am building a form and table. In the id
field I need to have a autonumber generated in this format. yy-nnnn.
Where yy is the last 2 digits of the year and nnnn is sequencial
numbers. Also the "-" needs to be in between the two.

Any assistance is appreciated.

Thank you
Marc

 

[Jeff Boyce]

If you are considering storing this "number" in a single field, step back.
A tenant of good database design is "one fact, one field." You are
probably already capturing a date for your record ... use a query to return
the yyyy (or yy) portion of the year of that date.

You'll want to have the sequence number start over each year, I assume. You
need to "roll your own" sequence numbering function, since the Access
Autonumber data type does NOT start over each year, nor is it designed to
guarantee that it is sequential (just unique). Take a look at Google.com or
at mvps.org/access for "Custom Autonumber" to get an idea of how to do this.

To display something that looks like "yy-nnn", use a query and concatenate
the information.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

Hello everyone!

I am in need of some help. I am building a form and table. In the id
field I need to have a autonumber generated in this format. yy-nnnn.
Where yy is the last 2 digits of the year and nnnn is sequencial
numbers. Also the "-" needs to be in between the two.

Any assistance is appreciated.

Thank you
Marc

Do not use an Autonumber for this purpose... period. Autonumbers are
unique within the table, so they won't "start over" with the new year,
and they will very often have gaps.

What you can do in a Form is to use the Form's BeforeInsert event,
which fires the moment that a new record is started. Some simple code
would be:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strID As String
Dim iID As Integer
strID = NZ(DMax("[ID]", "YourTable", "[ID] LIKE '" & _
Format(Date, "yy") & "*'"), "00-0000")
iID = Val(Right(strID, 4)) + 1
Me!txtID = Format(Date, "yy") & Format(iID, "\-0000")
Me.Dirty = False 'force a save of the record
End Sub

This runs a slight risk that two users might get the same ID; there
are more complicated techniques to ensure that they don't.

John W. Vinson[MVP]