Create custom Autonumber

  • Thread starter Thread starter Maulwy
  • Start date Start date
M

Maulwy

Dear All,

how to create custom autonumber yyyymmddnnn, eq: 20090716001, 20090716002,
201001001, etc.

Thanks
 
Why would you? Realistlcally, a number such as that violates one of the
principles of database normalization in that it contains two separate pieces
of information in a single field. Have two separate fields. If you need a
number such as that for display purposes, concatenate the two fields
together as a calculated field in a query, and use the query wherever you
would otherwise have used the table.
 
Dear Doug,
In my simple logic, the autonumber that I mean is, will be unique.
Transactions that may occur, a maximum of 999 in 1 day. Thank you for the
answer.

Rgds,
Maulwy
 
Using two separate fields is even a better idea then!

In the BeforeUpdate event of your form, put code like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord = True Then
Me.Id = Nz(DMax("Id", _
"MyTable", _
"RecordDate = " & Format(Date, "\#yyyy\-mm\-dd\#")), 0) + 1
End If

End Sub

This assumes that you're storing the date in a field named RecordDate, and
Id is the unique value for the day.
 
Dear Doug,

I've tried what you teach, but does not like the results I want. The result
is what I want, when you have 3 transaction this days, then the number that
arises is 20090716001, 20090716002, 20090716003. Then, when tomorrow is a new
transaction, then the number that arises is 20090717001, so on. Thank you for
your help.

Rgds,
Maulwy
 
I may be reading this wrong or missing something, but I don't see the reason
for formatting the date in the Where condition. Not that it does any harm I
can see, but I wonder why do it. I would have expected maybe:

Me.ID = Nz(DMax("ID", _
"MyTable", _
"RecordDate = #" & Date & "#"),0) + 1

For display purposes it would then be:

Format([RecordDate],"yyyymmdd") & Format([ID],"\-001")

To the OP, note that a number created in this way will not be the primary
key field, as it is incrementing only the end of the number, so will restart
at 1 every day. An autonumber field could be used as the primary key
instead.
 
Dear Bruce,


BruceM said:
I may be reading this wrong or missing something, but I don't see the reason
for formatting the date in the Where condition. Not that it does any harm I
can see, but I wonder why do it. I would have expected maybe:

Me.ID = Nz(DMax("ID", _
"MyTable", _
"RecordDate = #" & Date & "#"),0) + 1

For display purposes it would then be:

Format([RecordDate],"yyyymmdd") & Format([ID],"\-001")
Click to expand...

Thank you for the answer.
I apologize for the less knowledge that I have. My basic thought is to
create ordinal numbers, which are build from the date of this order + the
transaction number, then this number will return from the beginning of the
next day. Eq: today we have 3 transaction, and the number is 20090716001,
20090716002, 20090716003. Next day, we have 2 transaction, and the number is
20090717001, 20090717002. Its arises and restart every day.
To the OP, note that a number created in this way will not be the primary
key field, as it is incrementing only the end of the number, so will restart
at 1 every day. An autonumber field could be used as the primary key
instead.
Click to expand...

It's unique. Why can not be used as the primary key?
May have other suggestions?

Rgds,
Maulwy
 
BruceM said:
I may be reading this wrong or missing something, but I don't see the
reason for formatting the date in the Where condition. Not that it does
any harm I can see, but I wonder why do it. I would have expected maybe:

Me.ID = Nz(DMax("ID", _
"MyTable", _
"RecordDate = #" & Date & "#"),0) + 1
Click to expand...

"RecordDate = #" & Date & "#" won't work if the user's Regional Settings
have the Short Date format set to dd/mm/yyyy. I never assume that their
Short Date format is set to mm/dd/yyyy.
 
You do realize, hopefully, that the Id field in the table is only supposed
to contain 1, 2, 3...

You need to use the concatenation Bruce mentioned else thread to reconstruct
the two fields into one for display purposes:

Format([RecordDate],"yyyymmdd") & Format([ID],"\-001")

Bruce is absolutely correct that ID cannot not be the primary key: you'll
have multiple rows in the table with ID = 1. However, the combination of
RecordDate and ID together can be the primary key. (remember that a primary
key is just a special case of Index, and an index can contain up to ten
separate fields)
 
Instead of using the date formatted, you could just return the date's numeric
value multiplied by 1000 and then add 1 to the result.

Nz(DMax("TheField","TheTable","TheField>Date()*1000 And
TheField<Date()*1000+999"),Date() * 1000) +1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Ah, so that's it. Thanks.

Douglas J. Steele said:
"RecordDate = #" & Date & "#" won't work if the user's Regional Settings
have the Short Date format set to dd/mm/yyyy. I never assume that their
Short Date format is set to mm/dd/yyyy.
Click to expand...
 
As Douglas pointed out, you are only storing the number 1, 2, 3, etc. You
are starting with 1 each day, so it is not unique. It is unique in
combination with the date, so you could combine ID and Date as the primary
key, or you could add a primary key field. To see the number in the format
you want, concatenate (combine) it as I showed in my previous posting.

Maulwy said:
Dear Bruce,


BruceM said:
I may be reading this wrong or missing something, but I don't see the
reason
for formatting the date in the Where condition. Not that it does any
harm I
can see, but I wonder why do it. I would have expected maybe:

Me.ID = Nz(DMax("ID", _
"MyTable", _
"RecordDate = #" & Date & "#"),0) + 1

For display purposes it would then be:

Format([RecordDate],"yyyymmdd") & Format([ID],"\-001")
Click to expand...

Thank you for the answer.
I apologize for the less knowledge that I have. My basic thought is to
create ordinal numbers, which are build from the date of this order + the
transaction number, then this number will return from the beginning of the
next day. Eq: today we have 3 transaction, and the number is 20090716001,
20090716002, 20090716003. Next day, we have 2 transaction, and the number
is
20090717001, 20090717002. Its arises and restart every day.
To the OP, note that a number created in this way will not be the primary
key field, as it is incrementing only the end of the number, so will
restart
at 1 every day. An autonumber field could be used as the primary key
instead.
Click to expand...

It's unique. Why can not be used as the primary key?
May have other suggestions?

Rgds,
Maulwy
Click to expand...
 
I don't believe that will work, John. Date() isn't going to return 20090716
for today, it's going to return 40010. Maybe

Nz(DMax("TheField","TheTable","TheField>CLng(Format(Date(),
"yyyymmdd"))*1000 And TheField<CLng(Format(Date(),
"yyyymmdd"))*1000+999"),CLng(Format(Date(), "yyyymmdd")) * 1000) +1
 
Read my opening statement.

"... you could just return the date's numeric value ..."

I realize that this will not give the poster a date but it will give the
poster a unique number that is tied to a specific date. And if the user
wanted to get the date back. They could use

CDate(TheField/1000)

As you and others have pointed out, the poster would probably be better off
using two fields to accomplish his goal. She could then use the two fields as
a multi-field primary key or just use an autonumber as a surrogate key field.

I happen to agree with that recommendation, but sometimes people just gotta do
it their way.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Right you are. And thinking about it, the alternative I counterproposed
(using CLng(Format(...))) wouldn't working, because the values are too large
for Long Integers.
 
Dear Ken,

What I mean is can I create a series of numbers derived from the system
date, transaction number (eq: 20090717001, which means that transactions that
occur in the first on 17 July 2009 transaction with the series number 1. Do
things like this may be made to? Because I never use a program, where the
number of transactions, such as I intended. Thank you for your help.

Rgds,
Maulwy

KenSheridan via AccessMonster.com said:
Maulwy:

Yes, it is unique and could function as a key, BUT:

1. If you already have a column such as TransactionDate in the table then
you are repeating the value of that by the first 8 digits of the structured
key. Consequently the table is open to inconsistent data as there is nothing
to stop the values in one or both of the two columns being amended
independently of each other. The design is thus a bad one.

2. If you don't have a separate TransactionDate column you are storing two
separate values in the structured key, the first being a value of attribute
type 'transaction date', the other being a value of attribute of type
'ordinal number per day'. The definition (taken from Chris Date) of First
Normal Form (1NF) is:

"A relvar is in 1NF if and only if, in every legal value of that relvar,
every tuple contains exactly one value for each attribute."

In the language of the relational model a relvar loosely equates to a table
definition, a tuple to a row, an attribute to a column. In your case the
'structured key' column would contain two values of different attribute types.
Not only is this a bad design in principle, therefore, but it would give rise
to the practical problem of having to derive a date value from the column
every time you need to know the transaction date.

By having two columns, one for the ordinal number per day (of integer number
data type) and another for the transaction date (of date time data type) then
both of the above objections are overcome. The primary key can be a
composite one of both columns, or you can use a surrogate primary key, e.g.
an autonumber, and uniquely index the other two columns (in combination, not
individually) as these constitute a 'candidate key'. The ordinal number per
day can be computed in the way Doug described.

Ken Sheridan
Stafford, England
Dear Bruce,


I may be reading this wrong or missing something, but I don't see the reason
for formatting the date in the Where condition. Not that it does any harm I
Click to expand...
[quoted text clipped - 7 lines]
Format([RecordDate],"yyyymmdd") & Format([ID],"\-001")
Click to expand...

Thank you for the answer.
I apologize for the less knowledge that I have. My basic thought is to
create ordinal numbers, which are build from the date of this order + the
transaction number, then this number will return from the beginning of the
next day. Eq: today we have 3 transaction, and the number is 20090716001,
20090716002, 20090716003. Next day, we have 2 transaction, and the number is
20090717001, 20090717002. Its arises and restart every day.
To the OP, note that a number created in this way will not be the primary
key field, as it is incrementing only the end of the number, so will restart
at 1 every day. An autonumber field could be used as the primary key
instead.
Click to expand...

It's unique. Why can not be used as the primary key?
May have other suggestions?

Rgds,
Maulwy
Click to expand...
Click to expand...
 
// revised //
things like this may be made to? Because I never use a program, where the
number of transactions, such as I intended.
Click to expand...

should be:
Because I ever use a program, where the number of transactions, such as I
intended.

Maulwy said:
Dear Ken,

What I mean is can I create a series of numbers derived from the system
date, transaction number (eq: 20090717001, which means that transactions that
occur in the first on 17 July 2009 transaction with the series number 1. Do
things like this may be made to? Because I never use a program, where the
number of transactions, such as I intended. Thank you for your help.

Rgds,
Maulwy

KenSheridan via AccessMonster.com said:
Maulwy:

Yes, it is unique and could function as a key, BUT:

1. If you already have a column such as TransactionDate in the table then
you are repeating the value of that by the first 8 digits of the structured
key. Consequently the table is open to inconsistent data as there is nothing
to stop the values in one or both of the two columns being amended
independently of each other. The design is thus a bad one.

2. If you don't have a separate TransactionDate column you are storing two
separate values in the structured key, the first being a value of attribute
type 'transaction date', the other being a value of attribute of type
'ordinal number per day'. The definition (taken from Chris Date) of First
Normal Form (1NF) is:

"A relvar is in 1NF if and only if, in every legal value of that relvar,
every tuple contains exactly one value for each attribute."

In the language of the relational model a relvar loosely equates to a table
definition, a tuple to a row, an attribute to a column. In your case the
'structured key' column would contain two values of different attribute types.
Not only is this a bad design in principle, therefore, but it would give rise
to the practical problem of having to derive a date value from the column
every time you need to know the transaction date.

By having two columns, one for the ordinal number per day (of integer number
data type) and another for the transaction date (of date time data type) then
both of the above objections are overcome. The primary key can be a
composite one of both columns, or you can use a surrogate primary key, e.g.
an autonumber, and uniquely index the other two columns (in combination, not
individually) as these constitute a 'candidate key'. The ordinal number per
day can be computed in the way Doug described.

Ken Sheridan
Stafford, England
Dear Bruce,



I may be reading this wrong or missing something, but I don't see the reason
for formatting the date in the Where condition. Not that it does any harm I
[quoted text clipped - 7 lines]

Format([RecordDate],"yyyymmdd") & Format([ID],"\-001")

Thank you for the answer.
I apologize for the less knowledge that I have. My basic thought is to
create ordinal numbers, which are build from the date of this order + the
transaction number, then this number will return from the beginning of the
next day. Eq: today we have 3 transaction, and the number is 20090716001,
20090716002, 20090716003. Next day, we have 2 transaction, and the number is
20090717001, 20090717002. Its arises and restart every day.

To the OP, note that a number created in this way will not be the primary
key field, as it is incrementing only the end of the number, so will restart
at 1 every day. An autonumber field could be used as the primary key
instead.

It's unique. Why can not be used as the primary key?
May have other suggestions?

Rgds,
Maulwy
Click to expand...
Click to expand...
Click to expand...
 
Dear Ken,

What I mean is can I create a series of numbers derived from the system
date, transaction number (eq: 20090717001, which means that transactions that
occur in the first on 17 July 2009 transaction with the series number 1. Do
things like this may be made to? Because I never use a program, where the
number of transactions, such as I intended. Thank you for your help.
Click to expand...

This is called an "Intelligent Key" - and unfortunately that's not a
compliment.

Storing multiple pieces of information in a single field is generally a Very
Bad Idea. A date is one kind of information; a sequential number is *a
different kind* of information, and they're incompatible.

Bear in mind that Access does not require that a Primary Key be a single
field. It's perfectly possible to have a Primary Key consisting of TWO (or
ten, for that matter) fields! If you wish, you could have the transaction date
as one component of the PK (in a Date/Time field) and a sequential number as
the second component (in a Number field); these could be concatenated for
display purposes with:

ShowTranID: Format([TranDate], "yyyymmdd") & Format([TranSeq], "000")

If you want to *count* transactions, though, don't use the primary key for the
purpose; instead, use a query and... count transactions. Storing the count in
the table is neither necessary nor a good idea; if an erroneous transaction
needs to be deleted your count will either be incorrect or you'll need to
renumber all your other transactions for that day (and some of them may have
already been printed on paper or sent by email or whatever).
 
Dear All,

Thanks for your answer. Its helping me to understanding this custom
autonumber.

Thanks & regards,
Maulwy

KenSheridan via AccessMonster.com said:
Maulwy:

You can do it using a single column as the primary key. This column should
be a text data type, not a number data type. Assuming the column is called
MyID and the table is called MyTable you can out code in the form's
BeforeInsert event procedure:

Dim varNextNumber As Variant

' get last number used for current day if any
varNextNumber = _
DMax("MyID", _
"MyTable", _
"Left(MyID,8) = """ & Format(VBA.Date, "yyyymmdd") & """")

' if existing record(s) for current day then
' add 1 to last value,
' otherwise start new sequence for current day
If Not IsNull(varNextNumber) Then
MyID = varNextNumber + 1
Else
MyID = Format(VBA.Date, "yyyymmdd") & "001"
End If

Or in the form's BeforeUpdate event procedure:

Dim varNextNumber As Variant

If Me.NewRecord Then
' get last number used for current day if any
varNextNumber = _
DMax("MyID", _
"MyTable", _
"Left(MyID,8) = """ & Format(VBA.Date, "yyyymmdd") & """")

' if existing record(s) for current day then
' add 1 to last value,
' otherwise start new sequence for current day
If Not IsNull(varNextNumber) Then
MyID = varNextNumber + 1
Else
MyID = Format(VBA.Date, "yyyymmdd") & "001"
End If
End If

The first will show the number in the MyID control in the form as soon as you
start to enter data in any other bound control on the form, the second will
insert the new number when the record is saved. The first is more prone to
conflicts in a multi-user environment, however.

While the above will do what you want I would still recommend that you use
two separate columns for the date and the ordinal number per day for the
reasons I gave previously. If you do use the one column as a structured key,
however, its important that you DO NOT also have another column for the date
as this would leave the table at too great a risk of inconsistent data.

Ken Sheridan
Stafford, England
Dear Ken,

What I mean is can I create a series of numbers derived from the system
date, transaction number (eq: 20090717001, which means that transactions that
occur in the first on 17 July 2009 transaction with the series number 1. Do
things like this may be made to? Because I never use a program, where the
number of transactions, such as I intended. Thank you for your help.

Rgds,
Maulwy
Maulwy:
Click to expand...
[quoted text clipped - 59 lines]
Rgds,
Maulwy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top