Autonumber

Is is possible to create an AutoNuber that will have the numberic form of
the date with a 5 digit autonumber EG

07250600001

Any help woudl be greatly appreciated

No, you can't do that using an autonumber field. The example you show has
several problems.
1. Numeric fields do not carry leading zeros. To do so, it would have to be
a text field which, of course cant be autonumber
2. If it remains numeric, dates could not always be deciphered.
is 1122006 January 12, 2005 or is it November 2, 2006?
3. It will not sort correctly.

You would be well advised to use an Autonumber field for the incrementing
number and a date/time field to carry the date. It is possible to have
indexes that include mutiple fields of different data types. The same is
true of the primary index.


"Rodney James" wrote:

Is is possible to create an AutoNuber that will have the numberic form of
the date with a 5 digit autonumber EG

07250600001

Any help woudl be greatly appreciated

Thanks

What if adjusted how I did the Date to get around the leading zero problem

Using your example

January 12, 2005 = 20050112
November 2, 2006 = 20061102


"Klatuu" wrote in message
...
No, you can't do that using an autonumber field. The example you show has
several problems.
1. Numeric fields do not carry leading zeros. To do so, it would have to
be
a text field which, of course cant be autonumber
2. If it remains numeric, dates could not always be deciphered.
is 1122006 January 12, 2005 or is it November 2, 2006?
3. It will not sort correctly.

You would be well advised to use an Autonumber field for the incrementing
number and a date/time field to carry the date. It is possible to have
indexes that include mutiple fields of different data types. The same is
true of the primary index.


"Rodney James" wrote:

Is is possible to create an AutoNuber that will have the numberic form of
the date with a 5 digit autonumber EG

07250600001

Any help woudl be greatly appreciated

You still can't use that in an autonumber field.
You should just use a Date/Time field for the date.
If you want to put the two together for display purposes:
Format(SomeDate,"yyyymmdd") & Format(SequenceNumber,"00000")

BTW, sorry for the typo. in my previous example, both years should have been
2006. If I every learn to type, I will be dangerous

"Rodney James" wrote:

Thanks

What if adjusted how I did the Date to get around the leading zero problem

Using your example

January 12, 2005 = 20050112
November 2, 2006 = 20061102


"Klatuu" wrote in message
...
No, you can't do that using an autonumber field. The example you show has
several problems.
1. Numeric fields do not carry leading zeros. To do so, it would have to
be
a text field which, of course cant be autonumber
2. If it remains numeric, dates could not always be deciphered.
is 1122006 January 12, 2005 or is it November 2, 2006?
3. It will not sort correctly.

You would be well advised to use an Autonumber field for the incrementing
number and a date/time field to carry the date. It is possible to have
indexes that include mutiple fields of different data types. The same is
true of the primary index.


"Rodney James" wrote:

Is is possible to create an AutoNuber that will have the numberic form of
the date with a 5 digit autonumber EG

07250600001

Any help woudl be greatly appreciated

Its cool you have the same affliction I do as well.

If I every learn to type, I will be dangerous


So then there really is no formatting to the AutoNumber field?

"Klatuu" wrote in message
...
You still can't use that in an autonumber field.
You should just use a Date/Time field for the date.
If you want to put the two together for display purposes:
Format(SomeDate,"yyyymmdd") & Format(SequenceNumber,"00000")

BTW, sorry for the typo. in my previous example, both years should have
been
2006. If I every learn to type, I will be dangerous

"Rodney James" wrote:

Thanks

What if adjusted how I did the Date to get around the leading zero
problem

Using your example

January 12, 2005 = 20050112
November 2, 2006 = 20061102


"Klatuu" wrote in message
...
No, you can't do that using an autonumber field. The example you show
has
several problems.
1. Numeric fields do not carry leading zeros. To do so, it would have
to
be
a text field which, of course cant be autonumber
2. If it remains numeric, dates could not always be deciphered.
is 1122006 January 12, 2005 or is it November 2, 2006?
3. It will not sort correctly.

You would be well advised to use an Autonumber field for the
incrementing
number and a date/time field to carry the date. It is possible to have
indexes that include mutiple fields of different data types. The same
is
true of the primary index.


"Rodney James" wrote:

Is is possible to create an AutoNuber that will have the numberic form
of
the date with a 5 digit autonumber EG

07250600001

Any help woudl be greatly appreciated

You cannot format the autonumber in the table. Don't confuse what is stored
in tables with how we humans view the data. Using the two fields (Autonumber
for the sequencing and Date/Time for the date) is the best way to handle
this. Remember, you can set your primary key to be both of these fields.
Just be sure to put the date/time field before the autonumber field. Now
your table will sort by date and sequence number.

Now, there is another issue. A true Autonumber field will not restart at 0
for each change in date. So if the last entry you had for July 8, 2006 has
an autonumber of 10, the first entry for July 9, 2006 would not be 1, it
would be 11. If you want to start with 1 for each day, then an auto number
field will not work for you. It would need to be a Long data type. You
would then have to handle the sequencing programmatically, but that is no big
issue. Here is how you would do that:

NextNum = Nz(DMax("[SeqNum]", "SomeTable", "[TheDate] = #" & _
Me.MyDateField & "#"), 0) + 1

What the above does is find the highest current Sequence Number for the date
in the date control on the form and add 1 to it. If the date is not in the
table, DMax will return Null. So in the formula, Null will be converted to 0
so when you add 1 to it, it will return 1.


"Rodney James" wrote:

Its cool you have the same affliction I do as well.

If I every learn to type, I will be dangerous


So then there really is no formatting to the AutoNumber field?

"Klatuu" wrote in message
...
You still can't use that in an autonumber field.
You should just use a Date/Time field for the date.
If you want to put the two together for display purposes:
Format(SomeDate,"yyyymmdd") & Format(SequenceNumber,"00000")

BTW, sorry for the typo. in my previous example, both years should have
been
2006. If I every learn to type, I will be dangerous

"Rodney James" wrote:

Thanks

What if adjusted how I did the Date to get around the leading zero
problem

Using your example

January 12, 2005 = 20050112
November 2, 2006 = 20061102


"Klatuu" wrote in message
...
No, you can't do that using an autonumber field. The example you show
has
several problems.
1. Numeric fields do not carry leading zeros. To do so, it would have
to
be
a text field which, of course cant be autonumber
2. If it remains numeric, dates could not always be deciphered.
is 1122006 January 12, 2005 or is it November 2, 2006?
3. It will not sort correctly.

You would be well advised to use an Autonumber field for the
incrementing
number and a date/time field to carry the date. It is possible to have
indexes that include mutiple fields of different data types. The same
is
true of the primary index.


"Rodney James" wrote:

Is is possible to create an AutoNuber that will have the numberic form
of
the date with a 5 digit autonumber EG

07250600001

Any help woudl be greatly appreciated